Search code examples
databasedatabase-designprimary-keyfunctional-dependencies

Should I create a table with multiple candidate keys?

Suppose I have a table:

MyTable(AttrA, AttrB, AttrC)
Functional dependencies: (AttrA, AttrB) -> AttrC, (AttrA, AttrC) -> AttrB

And I choose (AttrA, AttrB) as primary key. So this design is good or bad?

Solution

  • If this is what the business situation is like, and this solution accurately refelcts the business scenario, then what could possibly be "bad" about it ?

    EDIT

    I assumed you were aware that such designs might get you into problems for certain "swapping" updates, but perhaps it would have been better to state this explicitly. The simplest case of such a "swapping" update is when you need to change from {A,B,C} {A,C,D} to {A,B,D} {A,C,C}. What ways are available to overcome such problems depends on your DBMS. And of course the fact that certain updates may be problematic to achieve in practice doesn't render the logical structure of your database invalid per se.

    Apologies for mentioning this only belatedly.