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

  • 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. – A-K Oct 21 '13 at 13:31

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.

  • 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 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. – Seph 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. – Seph 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 '16 at 16:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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