SQL: Relational DB design - multiple FKs to same table

I was hoping someone开发者_StackOverflow中文版 could tell me if this would be bad database design:

Let's say I have two tables:

Person  
Contract

And let's say I get two types of contracts, a single contract (which involves only one person) and a joint contract (which involves two people). There are no other permutations.

It would be the simplest to set up the Contract table with two FK fields, one of which is nullable, i.e.:

Contract:

ContractID     Description               PersonID      PersonID_Second
1              Single person contract    1112          NULL
2              Joint contract            1073          900

Is this a bad idea?

Thanks

Karl

"Is this a bad idea?" - Generally, yes. Because what happens when you need 3, 4 people on a contract?

Instead, create a many-to-many join table between Contract and Person, ContractPerson, containing ContractId, PersonId

Two more cents: Are the relationships between the two Persons and the Contract equivalent? Is there a "first" person and a "second along for the ride" person, or do they both share equal importance with regards to this contract? And is the distinction between one-person and two-person (and possible future N-person) contracts an important attribute of the contract?

• If relationships are equivalent, then go with Mitch's many-to-many solution.
• If there is an important distinction between first and second, Parched Squid's [saying that that made my morning better!] multiple FK fix would work well.
• But if you forsee N-person contracts, stick with the many-to-many, with an extra attribute in the relationship table indicating "which" relationship is being designated.
• And if you need to sort through your contracts to find or sort by the 1, 2, or N-person ones, it might be more convenient to have an extra attribute indicating type of contract, rather than implementing lots of joins and null-checking logic to count each contract's related persons.

I agree with Mitch that, all things being equal, the case you describe is better served with an M:N relationship.

However, to the generic question of whether "multiple FKs to the same table" is inherantly bad: I don't think so. For example, if Person and SecondPerson had fundamentally different purposes, you'd name the fields after the purpose, not the table they point to. So instead of PersonID, PersonID_Second, you'd call them SalesDudeID and ManagerID.

In that case, it would be less ideal to use an M:N table because it would be less clear which one was the SalesDudeID and which was the ManagerID. Furthermore, the question "what happens if there are 3 or 4" can be inversed not to suggest the schema isn't extensible, but that an M:N schema would allow invalid data (if 3 and 4, as you say, are illegal).

Again... I agree with Mitch to the specific question. I just read the question as though Karl simplified the use case for clarity and wanted to speak to a more general question which he may or may not have meant. +1 to Mitch.