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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top


  • Lists
  • Leads

What I am trying to achieve is a scenerio where in the relationship between lists and leads the following constraint is applied: A list can have many leads, but a lead can only belong to one list.

Standard way of doing this is a simple non-identifying one-to-many relationship.

enter image description here

Another way I'm doing this is to create a hybrid one-to-one and a one-to-many relationship.

enter image description here

In both cases a list can contain many leads, but a lead can only exist in one list. Does anyone see any advantages or disadvantages to the second method? Any differences between the two methods? Any alternatives?

share|improve this question
What does the second design gain you? I can't see any improvement over the first design TBH. – Simon Righarts Oct 20 '13 at 20:43
Your first approach is simpler, and it gets the job done. So I would go for it, unless there are additional requirements you did not describe in your post. – A-K Oct 21 '13 at 13:31
up vote 0 down vote accepted

There are very few scenarios in which you would probably benefit from your second design:

One is if there is a sub-typing scenario where only certain a certain sub-type had the relationship (and the super-type doesn't) and the sub-type has other additional fields, besides the foreign key.

Another might be where the existence and value of the foreign key needs to be controlled for security reasons at a different level of granularity than the rest of the entity containing the foreign key. Most DBMSs can handle security per table. Most don't handle security per column.

share|improve this answer
Another good reason to go with method two is if I decided to make the lists_list_id FK optional. By moving the relationship to a pivot table, then I avoid several millions of records of potential null values in that column in the lead entity. – SephVelut Oct 21 '13 at 7:18
@SephVelut - Avoiding nulls is only important if you have a performance or space problem and your foreign key is populated only a small fraction of the time. A lot of people like to avoid sparse table, but extending that notion to an optional column is overkill. – Joel Brown Oct 21 '13 at 10:52
Exactly, which is why I prefer the latter approach. Mind you multiple tables is not a bad thing. Relational Databases are at their best when broken into discrete units. – SephVelut Oct 21 '13 at 11:18
I would also add that it creates a layer where you can separate your concerns. Say I had boolean type columns inside lead. Where very shortly it can become unwieldy with 4 or 5 flags columns. Instead normalizing to separate tables with the corresponding relationships helps keep entities organized and light. – SephVelut Oct 22 '13 at 8:11
I just ran into such case. I have a users table and a user_types table, but only a certain user_type should also have a relationship to homes table. Where would that relationship go? I don't want a home_id column in my users table. Do I create a pivot table for this then? – Vic May 26 at 16:36

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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