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.

1. yes
2. yes
3. depends
4. no
5. yes
6. yes

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

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

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?

it's simpler (as well as shorter)

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

No

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.

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

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)?

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.

Of course not; I prefer to do my own thinking. With that said, however, I do appreciate the guidance you and others have offered.

Ah, yes. Point taken.

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)?

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.

Thanks! It's making sense.