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.

Entities:

  • 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
2  
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. –  AlexKuznetsov Oct 21 '13 at 13:31
add comment

1 Answer

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
add comment

Your Answer

 
discard

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.