How to store a Preferred Contact? [duplicate]

Question

I'm creating a CRM type application for a client. They need to store information about Companies and information about Contacts within those companies. Each Company can have multiple Contacts associated with them but only one of the Contacts in each Company can be the "Preferred" contact.

So I have the following two tables:

Company
PK: ID
Name
Tel
etc

Contact
PK: ID
FK: CompanyID
Name
Tel
etc

I can see two ways to add the Preferred Contact attribute. I could put a FK:PreferredContactID field on the Company table or I could put a PreferredContact boolean field on the Contact table. The former means that I would have a circular relationship the latter means that I have to have a constraint "Only one Contact within a Company can be the Preferred".

My question is which of these is the best option and why or is there a better way?

Answer 1

I would not be worried about a circular reference: they are common in data modelling (any tree/forest/graph structure for instance) so your tools should cope with it.

The "property of the contact" method is probably what I would default to, as it is the sort of thing where the rules are likely to change (if a company decides two people should get all important comms they might want to list two preferred contacts) and this method feels more flexible in that respect.

Other than that, from a design point of view as your rules currently stand (at least the ones that you've mentioned) either works as well from an integrity point of view as both almost enforce the "at most one per company rule" (though "property of a contact" requires an extra constraint/index to do so) but in either case you need to wrap custom some logic either as triggers or functions in other layers of your application logic, to enforce the rest of the rule (enforcing that there should be at least on preferred contact if any contacts exist, dealing with people who move between companies if the model considers that (rather than treating such a move as a delete and a new person being added), and so forth).