Mutiple FK columns all pointing to the same parent table - a good idea?

Question

For those of you who live and breath database design, have you ever found compelling reasons to have multiple FK's in a table that all point to the same parent table?

We recently had to deal with a situation where we had a table that contained six columns which were all FK columns to the same parent table. We're debating whether this indicates a poor design on our part or whether this is more common than we think.

Thanks very much.

Answer 1

It depends a lot on the situation. There are often times where you need to have it like that, other times, a redesign is in order. The first good usage that comes to mind is a messaging system for a website, where the user_to and user_from fields would both point back to user_id in the users table.

For 6 pointing back, though, I'd think something needs to be redesigned, but without knowing the specifics, it's impossible to say.

Answer 2

We do it occasionally when the PK is for the person table and we need to store details about two different classes of people in the same table. If the six columns were legitimately different information (and unlikely to expand to seven columns later), it might be ok, but more than two and I'd be looking at whether a related table was what was really needed.

Answer 3

This really can't be analyzed in a vacuum (i.e., without seeing the requirements). The main thing is to figure out if those 6 pieces of data are related to each other.

A column set such as: Item1, Item2, Item3 would clearly be doing it wrong (use a junction table), but if the meanings of each column are unrelated to each other, then it's fine, even if it looks a bit strange.

Answer 4

I can't imagine why you would need 6 fields pointing to the same parent record...sounds screwy like you thought. You say 'poor design on our part', did your company design the table this way?

Answer 5

I have some examples of multiple FKs between 2 tables.

Whether it's correct or not in your case, we probably can't say without more info possibly

One example that you see often:

Let's say I have a table of stuff with key stuffID. I may have a child table with stuffID1, stuffID2 to capture pairs. Or triplets with 3 FK columns.

Answer 6

Well, there can be IMO table with columns like:

Owner, CreatedBy, LastModifiedBy, AcceptedBy, ProposedBy , which can point to one User table

Answer 7

Having an online shop database, there should be one table containing addresses and one table containing the orders - now in order's there's two fk's to the addresses table, one containing the shipping and one containing the billing addresses key.

Answer 8

Table Persons {personID otherpersonattributes ...} Table InterPersonRelationships {personID1 personID2 relationshiptype}

It is very natural to have two distinct FK's to the same parent table in this kind of situation.