1

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?

Improve this question
2
  • 2
    What does the second design gain you? I can't see any improvement over the first design TBH.
    – Simon Righarts
    Commented Oct 20, 2013 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
    Commented Oct 21, 2013 at 13:31

1 Answer 1

Reset to default
1

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.

Improve this answer
6
  • 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.
    – Seph
    Commented Oct 21, 2013 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
    Commented Oct 21, 2013 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.
    – Seph
    Commented Oct 21, 2013 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.
    – Seph
    Commented Oct 22, 2013 at 8:11
  • 1
    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
    Commented May 26, 2016 at 16:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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