Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2009
    Posts
    104

    ?s on linking tables.

    Thanks for viewing this post. Could I ask someone(s) to answer these primitive questions for me?

    1. Can I legitimately link three tables with a linking table?

    2. If so, this table would create a three-part compound key, correct? (Is there a better conventional term?)

    3. Would creating a surrogate primary key for this linking table -- or any other linking table, for that matter -- be advisable?

    4. Do I violate any normal form by adding fully-dependent attributes to a three-way linking table (given one is legit)? (That is, each combination of the three table.fields will take on different attributes.) (As I have it, 3nf excludes partially dependent fields -- fields/attributes associated with only one of the keys in a compound key.)

    For example, a different operator (with different operating policies and habits), using a particular truck with a particular trailer would get a certain range of fuel efficiency. The same operator with the same truck and another trailer (maybe much larger or smaller) would get a different fuel efficiency. A different operator with the same truck/trailer would get yet another fuel efficiency (based on policy -- governed speed versus "hell-bent-for-glory" or based on physical modifications of equipment). So a dependent attribute would be fuel effiency.

    5. My understanding of the 2nd normal form is that a. it excludes multi-value keys, and b. compound keys are multi-value keys. Am I correct on this?

    6. Can a linking table act as a parent table?



    Thanks again for any help.
    Last edited by rbfree; 02-18-09 at 15:07.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. yes
    2. yes
    3. depends
    4. no
    5. yes
    6. yes

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2009
    Posts
    104
    Thanks Rudy,

    Could I get a further clarification on whether or not creating a surrogate primary key for this linking table -- or any other linking table, for that matter -- would be advisable:

    As I understand things, this approach would bring me into compliance with 2NF and would make updates easier.

    But, what are some counter-considerations? (For example, is increased abstraction a significant problem? For instance, if my P keys are based on meaningful tuples and I replace this combination with an abstract surrogate, the resulting P-key is more abstract.)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    adding a surrogate key in and of itself does not alter the basic "normality" of a table

    if a table is in 2NF already, adding a surrogate key will not change that

    if a table is not in 2NF, adding a surrogate key will not change that

    the only time* a surrogate key for a linking table is advisable is if the linking table itself has child tables


    * applies to all serious modellers except those who routinely slap a surrogate key on every single table they run across, and we won't say how we feel about those sorts of people here

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2009
    Posts
    104
    Thanks, R. These two statements are helpful. Could I request one more follow-up? (I guess I can always request...)

    Why is an S-key advisable for such a condition?


    Quote Originally Posted by r937
    the only time* a surrogate key for a linking table is advisable is if the linking table itself has child tables


    * applies to all serious modellers except those who routinely slap a surrogate key on every single table they run across, and we won't say how we feel about those sorts of people here


  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's simpler (as well as shorter)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Like all normal forms, 2NF is about keys and dependencies. It is not concerned with whether a key is compound or not.

    When representing a three-way relationship you should be aware of connection trap problems and violating 5NF. If you need to read the background to those things, see Terry Halpin's book, "Information Modeling and Relational Databases" or Fabian Pascal's "Practical Issues in Database Management".

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by r937
    5. yes
    No

    Quote Originally Posted by rbfree
    5. My understanding of the 2nd normal form is that a. it excludes multi-value keys, and b. compound keys are multi-value keys. Am I correct on this?
    No. 2nd normal form says that if you have a relation with a compound key, then there cannot be a non-key attribute in the relation that only depends on part of that key.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, it said excludes

    i misunderstood again

    but dportas says 2NF "is not concerned with whether a key is compound or not"

    i think i have to go lie down for a while ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2009
    Posts
    104
    Thanks for these refs. I'll order both of them (err, if I can afford them).

    Would you recommend linking two the first two tables with a two-way linking table, then linking the two-way table to the third table (in lieu of creating a three way linking table)?

    Quote Originally Posted by dportas
    Like all normal forms, 2NF is about keys and dependencies. It is not concerned with whether a key is compound or not.

    When representing a three-way relationship you should be aware of connection trap problems and violating 5NF. If you need to read the background to those things, see Terry Halpin's book, "Information Modeling and Relational Databases" or Fabian Pascal's "Practical Issues in Database Management".

  11. #11
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by rbfree
    Would you recommend linking two the first two tables with a two-way linking table, then linking the two-way table to the third table (in lieu of creating a three way linking table)?
    All I can say is that I would create a model that accurately reflects the business requirements and the reality being modelled. The fact that you are asking these questions suggests you haven't done enough analysis yet but I'm in no position to do it for you.

    Try to avoid the term "linking" when discussing relational database modelling. Referential relationships are constraints not links or pointers.

  12. #12
    Join Date
    Feb 2009
    Posts
    104
    Of course not; I prefer to do my own thinking. With that said, however, I do appreciate the guidance you and others have offered.

    Quote Originally Posted by dportas
    you haven't done enough analysis yet but I'm in no position to do it for you.
    Ah, yes. Point taken.

    Quote Originally Posted by dportas
    Referential relationships are constraints not links or pointers.
    I'm trying to understand an idea here and some related terminology... piecing this together from several texts that use terminology inconsistently (among them) and sometimes seem to conflict in meaning. So, thanks for all patience, past present and future.

    1. Anyway, in a "reality" situation, a production unit will include one worker, one truck, and one piece of equipment. Each worker comes from a set of available workers. Each truck comes from a set of available trucks, and each piece of equipment comes from a set of available pieces of equipment.
    (Truth is, in "reality," some trucks won't work with some pieces of equipment, but I'm trying to grasp a principle. The example is fabricated.)

    In my conceptual model, a "workers" entity/table corresponds with the set of workers, "equiptypes" corresponds with the equipment, and "trucktypes" with trucks. A table/entity called "productionunits" will correspond to the production unit. ("productionunits is an aggregated supertype, as one text puts it, made up of the subtypes workers, equiptypes, and trucktypes.)

    So,

    1. Am I correct in thinking that the relationship between workers, equiptypes, and trucktypes is a "ternary relationship"?

    2. Given that I do have a ternary relationship, would it be preferable to break the above relationship into two binary relationships? For example, would one want to relate trucktypes to equiptypes with a linking table (trucktypes_equiptypes), and then relate trucktypes_equiptypes to workers through a second linking table (trucktypes_equiptypes_workers)?

  13. #13
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    1. Yes.

    2. I doubt it. Create extra relations only if you need to eliminate some non-key join dependency - for example if there was a multi-valued dependency between equiptypes and trucktypes. Actually you suggested that their IS some dependency between those things, but that doesn't necessarily imply a non-key join dependency. There are formal methods for validating these things but you need to define all the business rules first. Identify the dependencies and then the model should flow from that.

  14. #14
    Join Date
    Feb 2009
    Posts
    104
    Thanks! It's making sense.

Posting Permissions

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