I have a database layout that has gotten kind of hairy for me. These tables hold the information that comes from a form that counselors will use when they sign up to be part of a counseling. It makes sense to me up to the point of the title table. You see, a counselor can have a title, but he could have a different title at a different practice location. I'm not sure if I have joined the tables correctly to accomplish that.

you're right, something's not quite right

i love whatever tool you're using to do those models (what is it, please? it looks kinda mac-ish)

examine each table by itself and verify that the attributes (normal font) depend on the primary key (bold font)

in the title table, the pk is titleID, but the attributes years and startDate depend on more than titleID, they depend on which counselor it is too

in counselorTitle table, practiceID is not part of the pk and this would mean a counselor could not have the same title at different practices

change counsellorTitle to counsellorPracticeTitle, make all three IDs part of the pk, and move years and StardDate as attributes

counsellorPracticeTitle is also a child of cousellorPriactice

practiceTitle could be retained, but i don't see what usefulness it would have (a list of all valid titles at any practice? usually you would not care, beyond those rows implied by counsellorPracticeTitle)

i don't know what specialties your Specialty tables model, but i would expect it to be for the purposes of classifying types of practice, which i would not expect to vary by location, so Specialty should perhaps relate to Practice instead

hope that helps

rudy

I concur with much of what rudy said.

Only thing to consider is the Practice Location according to the initial description.

It is possible that PracticeLocation, Counselor and Title may create a new table PracticeLocationCounselorTitle. It's a mouthful, but if Practice location is the determinant of the title as opposed to practice itself this is the table to build.

Practice(PracticeID)
Location(LocationID)
PracticeLocation(PracticeLocationID, PracticeID, LocationID)
Counselor(CounselorID)
Title(TitleID)

PracticeLocationCounselorTitle(PracticeLocationID, CounselorID, TitleID)

Note the use of a surrogate key for PracticeLocation.

"Note the use of a surrogate key for PracticeLocation"

yeah, i noticed, and i dislike it

i can see why you want it -- as the parent for the PracticeLocationCounselorTitle table -- but i dislike the surrogate, it adds unnecessary complexity

and within the logical design stage, it is distracting if not wrong to consider surrogate keys, which are really within the purview of physical design

i like this more --

PracticeLocationCounselorTitle(PracticeID, LocationID,
CounselorID, TitleID)

in any case i expect the model will not need to know that a counsellor may have had different titles at different locations while working for the same practise

inlikely, in my mind, that this granularity of possibile title precision will be needed

but then, i don't really know the counselling business


rudy

Surrogate keys are very much a part of logical design. In fact, they stem the tide of unnecessarily large and confusing composite keys, reducing complexity.

If there are only five Practice Locations why make all the combinations of Practice and Location available to PracticeLocationCounselorTitle? Use the surrogate key and offer only five. It's business logic not a physical constraint.

In fact, both of the solutions offered by rudy and I are based on our intuitive understanding of the business logic. Either solution could be right.

The business rules for this problem have to be elaborated on.

i love this discussionjust because you have a table PracticeLocation table does not require you to record every combination, i.e. have a row for each practice each location

you can just as easily use the composite natural key instead of the surrogate, and still offer only five

ta da

and as for "unnecessarily large and confusing composite keys", all i can say is that natural keys are, if anything, the least possible "large" and it is the surrogate key that makes the table even larger

as well, you would need an additional unique constraint, manifested as a second index, on the practise location composite anyway

as well, natural composites are not confusing, what is confusing is when you are looking at some table and all it has is a single column surrogate foreign key, you cannot see what its relationships are without tracing back to the parent which owns the surrogate primary

which means an additional table in a join in most cases

i would say that is confusing, and best left for physical design

I'm in my element.

Hate to disagree with you but I would advocate the use of surrogate keys in all tables. Natural keys are a natural menace to the flexibility of design and the flexibility of data maintenance. Storage and memory are cheap and if you'd sacrifice the quality of your database to save a few lousy meg then you'll find yourself in the jobs that require it.

Composite keys are rarely necessary. To have to add a number of columns to every table to figure out where I am at instead of using a single composite key with an informative name is ludicrous. Make as many relationships independent as possible.

And as far as physical design goes, surrogate keys are both a logical and physical design element. Physical design is about the physical constraints of the host database. But that knowledge does not seem to be in evidence.

Complexity, wait until you develop the interface for the four column composite key solution. As opposed to using a business rule when it was needed.

Ultimately, this is why we have Key-Based modeling and Fully Attributed Modeling. Design with all your composites in your Key-Based Logical model. Then proceed with your Fully Attributed Logical model. Save Physical Modeling for the physical constraints of the host RDBMS.

"Storage and memory are cheap and if you'd sacrifice the quality of your database to save a few lousy meg then you'll find yourself in the jobs that require it." -- dude, it's not about storage, it's about processing cycles, and extra joins just eat those up; avoid extra joins and you're laughing

"Composite keys are rarely necessary" -- but if you use a surrogate key, you also have to declare an additional unique constraint, twice as much to update on inserts

"To have to add a number of columns to every table to figure out where I am at instead of using a single composite key with an informative name is ludicrous" -- a single composite key?? you have to slow down, man, you're not making sense, a composite key isn't single, nor does it require extra columns!! surrogates require extra columns!!

"Complexity, wait until you develop the interface for the four column composite key solution. As opposed to using a business rule when it was needed" -- doesn't make sense, business rules have nothing to do with whether a user interface is based on a composite key

let's see your four column option that offers the user only one dropdown list of surrogate keys to choose from

say, i wonder what kannis thinks of all this?

Talking past one another.

More or less joins are not the answer in a logical model. Saving processing cycles are not the goal in a logical model. Unique constraints aren't the subject of a logical model.

Composite keys consume multiple columns in their child tables. A surrogate key consumes one column.

Business rules do indeed dictate whether this:

Practice(PracticeID)
Location(LocationID)
PracticeLocation(PracticeLocationID, PracticeID, LocationID)

Counselor(CounselorID)
Title(TitleID)
PracticeLocationCounselorTitle(PracticeLocationID, CounselorID, TitleID)

Or:

Practice(PracticeID)
Location(LocationID)
Counselor(CounselorID)
Title(TitleID)
PracticeLocationCounselorTitle(PracticeID, LocationID, CounselorID, TitleID)

Is used.

I offer a three column option with a drop down menu that offers a set of practice location pairs, a dropdown with a set of titles and a drop down with a set of Counselors.

The solution you originally suggested offered three dropdowns without considering Practice location at all.

The second looks simpler, but it isn't.

I would have to repeatedly choose my Practice Location pair instead of the damn database being smart enough to do the work for the user and remember the pairs.

> "Saving processing cycles are not the goal in a logical model"

then neither is storage space, which you are so sensitive about


> "Unique constraints aren't the subject of a logical model."

WTF OMG LOL

that says it all to me

you and i live on different planets

i'm going to let you have this one

good luck in your future endeavours


rudy

I'm going to take this one.

Business rules are the substance of a logical model.

Business rules are driven by the end user. Not storage or joins or cycles or physical requirements of unique constraints. That all comes later during the physical design.

http://www.amazon.com/exec/obidos/tg...books&n=507846

I'm still here.