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?