Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

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?

share|improve this question

marked as duplicate by Jon Seigel, Max Vernon, RolandoMySQLDBA, Michael - sqlbot, Paul White Feb 12 at 5:48

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1  
See this: How to have a one-to-many relationship with a privileged child? Depending on the DBMS, you have various choices. –  ypercube Oct 14 '13 at 9:23
    
@ypercube thanks that does look like a duplicate of my question. –  Martin Brown Oct 14 '13 at 13:42
2  
I would have voted to close as duplicate but that question is tagged with "sql-server" while you have no tag. Are you interested to implement this in a specific DBMS? –  ypercube Oct 14 '13 at 14:10

1 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).

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.