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:
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?