CODEWITHSUNDEEP
database-designforeign-keys
Randy Minder
Randy Minder

Reputation: 48522

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

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.

Upvotes: 3

Views: 261

Answers (8)

Erwin Smout
Erwin Smout

Reputation: 18408

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.

Upvotes: 0

dhh
dhh

Reputation: 4335

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.

Upvotes: 0

nothrow
nothrow

Reputation: 16178

Well, there can be IMO table with columns like:

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

Upvotes: 3

gbn
gbn

Reputation: 432667

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.

Upvotes: 0

Leslie
Leslie

Reputation: 3644

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?

Upvotes: 0

Jon Seigel
Jon Seigel

Reputation: 12401

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.

Upvotes: 4

HLGEM
HLGEM

Reputation: 96640

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.

Upvotes: 1

Tarka
Tarka

Reputation: 4043

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.

Upvotes: 6

Related Questions