Hi all

I have some questions about db designed and hope it is the right place to get the answer.


In the document said
"assign keys with business meaning is almost always a terrible design decision"

I am working a system that has a table contains bank card information
and if I use the card's number as the primiary key, is it wrong?

Should I use surrogated key? If I use surrogated for the bank card table, users
will still use the bank card no to make queries and if bank card no is not
the primary key, it may not be unique and require a full table scan for every
query.


Why composite key is not desirable? Again in my system, one bank card may have
multiple contact numbers assoicated with it. In the current design, the contact
numbers table has a composite key of "Bank card no + phone no". What's wrong with
the composite key here? Surrogated key doesn't seem to solve the problem here, either.


Have I misunderstood the meaning of the composite key?


Thanks!

You could choose to use the BankCard number as the primary key but its not a great choice as it has business meaning. Here is an unlikely senario - What happens if the Bank changes its prefix code (first 4 numbers) on all bank cards or a subset (say dropping all VISA cards). From a relationship prespective (who owns the cards) there will be a lot of data manipulation to do it. If you use a plain meaningless number then one change update to the bankcard no field and the is no impact on any relationships thus they do not need to be changed.

You can use a unique constraint (as I would expect you to) on the bankcard field. Assuming (as most do) the database uses an index as the constraint control you will be fine with your queries.

The composite key can be dropped as soon as you reorganise the bankcard table. The contact numbers will have three fields, Surrogated key, Relationship foreign key and the customer phone number.

Composite keys complicates the schema, makes it harder to query, harder to map (key generation strategies go out the door), harder to maintain in the longer term. Many-to-Many link tables use them otherwise, if you have a choice, avoid them.