Options for structuring multiple associations to a record

Question

This is 101 level stuff and I promise to read a book later, but in the meantime, given the following three tables:

Product
---------
ProductId (uniqueidentifier)


User
---------
UserId (uniqueidentifier)


Company
---------
CompanyId (uniqueidentifier)

If a Product can be assigned to different "owners" (i.e., a User, a Company, etc.), I can think of two table structures to accomplish this:

Solution 1 (1 table for all owners):

ProductAssignment
---------
ProductId,
OwnerId (constraint to ensure either UserId, CompanyId, etc.)

Solution 2 (1 table for each owner):

UserProduct
---------
UserId, 
ProductId

CompanyProduct
---------
CompanyId, 
ProductId

Solution 2 offers more of a "proper" model, but we have a lot of these scenarios and it's going to result in our database being littered with many dozens of these types of tables. It's also a pain to code for since I would need to create a new, redundant query each time a new owner table is added. I feel the maintenance and coding would be much easier for Solution 1, but maybe I'm wrong?

For what it's worth, most of the data access will be through LINQ to SQL (ASP.NET Web Forms project), and Entity Framework (ASP.NET MVC project). Very few sprocs and such.

• What pros and cons am I missing for each solution?
• Will bad things happen to me if I go with Solution 1? i.e., guys in black suits and sun glasses showing up at the office to make me "disappear?"
• Is there a Solution 3?

Answer 1

I'm not entirely sure if I'm right, but as far as I know solution 1 will not work since you can't add two foreign keys in the same column and even if it is possible the values will probably overlap, such as existing a UserId = 1 and a CompanyId = 1 causing you not to know which of them is the true one, furthermore thinking of a big solution you may even compromise the performance of a query that wishes only company owned or user owned. Solution 2 seems much more robust and a efficient design to me.

Answer 2

There are two ways you can achieve this with relatively little effort. Which is better depends on the nature of ownership.

Multiple owners

If a product can have more than one entity that owns it (two or more people sharing ownership, or a person and a company, or two or more companies) then I would model it with a single parent-child linking table with multiple columns to differentiate which type of owner is involved.

ProductOwner
------------
ProductOwnerID  PK Not Null
ProductID       FK 
UserID          FK
CompanyID       FK
...

So for each product, you would create one entry in the ProductOwner table for each owner. If the owner was a company, then the table would have values in the ProductOwnerKey, ProductID, and CompanyID fields, and the other ID field(s) would be empty.

So there would be one entry in this table for each owner of the product, with some fields always being null. Other columns might exist, like creation date, modify date, modify user ID, or etc...

Single Owner

If a product can only ever have a single owner, then I would add the foreign keys directly into the products table.

 Products
 --------
 ProductID  PK Not Null
 UserID     FK
 CompanyID  FK
 ...

So if there's only one owner for a product, then just put a foreign key value that links to each potential owner table directly in the products table and then only ever populate the appropriate FK value, leaving the other owner FKs null here.