Results 1 to 14 of 14

Thread: Database Layout

  1. #1
    Join Date
    Dec 2003
    Location
    Los Angeles, CA
    Posts
    2

    Talking Database Layout

    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.
    Attached Thumbnails Attached Thumbnails awarenet.gif  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  3. #3
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710

    Thumbs up

    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.
    Last edited by certus; 12-21-03 at 16:59.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "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

  5. #5
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i love this discussion
    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
    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

    ta da


  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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


  8. #8
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    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.
    Last edited by certus; 12-21-03 at 20:26.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "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


  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    say, i wonder what kannis thinks of all this?


  11. #11
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    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.

    let's see your four column option that offers the user only one dropdown list of surrogate keys to choose from
    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.
    Last edited by certus; 12-21-03 at 20:51.

  12. #12
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    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.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    > "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

  14. #14
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •