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.
Another way I'm doing this is to create a hybrid one-to-one and a one-to-many relationship.
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?
Best Answer
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.