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.
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
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.
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.
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
just 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
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