I have a database with a
Customers table, and two child tables
Contacts, as customers often have multiple locations and contacts.
However we also need a "primary address" and a "primary contact" for each customer that we use as the default.
My current design was to put a
PrimaryContactID and a
PrimaryAddressID in the
Customers table (both are nullable), but I was never very comfortable with this design - a parent table referencing the child table doesn't feel right.
The alternative approach I considered was an
IsPrimary bit field on the child tables. This would need a trigger that would unset the
IsPrimary value on the other records and maintain integrity but could lead to problems if a query tried to set multiple records as primary for the same customer: I'd have to write the trigger to fail in these situations.
Any ideas/comments or other approaches I should consider?