Multiple tables to single key in related table

[ITMA]

Can someone tell me if its good, bad or indifferent practice to have two or
more tables all relating to the same field in another table? When you see
examples in books or the Northwind file, the tables are daisy chained one to
the next, whereas I seem to require three tables all linking into the same
field on a fourth table. Does that indicate bad design?

On a related note, is it in practice the same to create a relationship from
one of the foreign keys to another foreign key that does itself relate to
the primary key in the fourth table - it just seems to look neater in the
relationships window!

 

[Dirk Goldgar]

Can someone tell me if its good, bad or indifferent practice to have
two or more tables all relating to the same field in another table?
When you see examples in books or the Northwind file, the tables are
daisy chained one to the next, whereas I seem to require three tables
all linking into the same field on a fourth table. Does that
indicate bad design?

On a related note, is it in practice the same to create a
relationship from one of the foreign keys to another foreign key that
does itself relate to the primary key in the fourth table - it just
seems to look neater in the relationships window!

I'm sorry, but I can't quite picture what you're talking about in either
case. Could you give examples? With respect to your first question,
it's certainly okay -- and extremely common -- to have several
"many-side" tables related to the same "one-side" table, so that the
"one-side" table's primary key field appears as a foreign key in more
than one other table. But I'm not sure if that's what you're asking
about or not. Examples would help.

 

[Guest]

Does that indicate bad design?
No. Your design may be perfectly valid. For example, suppose I have a
database that tracks people and subjects that these people are interested in.
Further, I wish to store multiple phone numbers (voice, cell, fax, etc.) and
multiple e-mail addresses. I would have a People table with a pkPersonID
primary key. I would also have three additional tables: Interests,
PhoneNumbers, and EMailAddresses. Each of these tables would contain a
fkPersonID as a foreign key. The pkPersonID field would be joined three times
to fkPersonID fields in the three different tables. Such a design would allow
me to enter an unlimited number of phone numbers (I'd include a ContactType
field to denote voice, cell, fax, etc), an unlimited number of e-mail
addresses and an unlimited number of interests for each person. This is a
perfectly valid design.

On a related note, is it in practice the same to create a relationship from

one of the foreign keys to another foreign key....

Ummm....I believe that creating a relationship between two foreign keys
would lead to an Indeterminate relationship.

it just seems to look neater in the relationships window!

I'm not sure what you mean by this ("looks neater"). Certainly it is a good
practice to establish relationships between tables in the relationships
window. If you tick off the option to "Enforce Referential Integrity", which
is something I recommend that you do, you will get the "1" and the sideways
"8" (ie. many) symbols included in the join line. You won't see these symbols
if you create your joins between tables in the query designer.

Tom
____________________________________

:

Can someone tell me if its good, bad or indifferent practice to have two or
more tables all relating to the same field in another table? When you see
examples in books or the Northwind file, the tables are daisy chained one to
the next, whereas I seem to require three tables all linking into the same
field on a fourth table. Does that indicate bad design?

On a related note, is it in practice the same to create a relationship from
one of the foreign keys to another foreign key that does itself relate to
the primary key in the fourth table - it just seems to look neater in the
relationships window!

 

[Albert D. Kallal]

Can someone tell me if its good, bad or indifferent practice to have two
or more tables all relating to the same field in another table?

If that field is the PK, then for sure the Parent table will often have
"many" child tables.

I have a screen shot of the relationship window here...you might take a
quick look. Also, note how some tables have a arrow head..and some don't.
That arrow head of course signifies a left join...of which about 90% of mine
are...

http://www.members.shaw.ca/AlbertKallal/Articles/PickSql/Appendex2.html

 

[Charlie Tame]

Can I just say thanks to you Tom, and to Albert, for answering a question I
had yet to ask, and I guess to ITMA (It's that man again?) for asking it!

Charlie

 

[Guest]

Hi Charlie,

You're very welcome. I'm glad we could help.

Tom
____________________________________

:

Can I just say thanks to you Tom, and to Albert, for answering a question I
had yet to ask, and I guess to ITMA (It's that man again?) for asking it!

Charlie