One-to-many relationship related to many tables

Question

I have a scenario where: there are two (or more) tables that represent independent items. lets say Users and Companies

Both of these tables need addresses stored. Each one can have one or more address

In a normal 1 to many scenario Addresses table woudl just have a UserId or a CompanyId creating a normal 1 to many relationship.

In this case i have a few approaches i can think of

1. the Addresses table could have both a UserId and a CompanyId and only one would be used for each record.

2. 2 keys could be used ObjectId and ObjectType So Object id would have a UserId or a CompanyId, and ObjectType woudl be User or Company

3. Create an ObjectTable and add ObjectId to Users and Companies. Addresses would then have an OjbectId

I do not really like any of these solutions. i am wondering what is the best approach here.

On another note i will most likely user linqtosql for my data access layer.

Answer 1

Go many-to-many:

An address table, with a unique synthetic id (e.g, an autoincrement).

A user_address table, with a unique synthetic id (e.g, an autoincrement), a user_id foreign key, and an address foreign key.

A company_address table, with a unique synthetic id (e.g, an autoincrement), a company_id foreign key, and an address foreign key.

(Note that if users (or companies) could have only one address, you just have an address_id foreign key in the user table (or teh company table). This is not your use case.)

Answer 2

I'm not sure about the implications regarding Linq-to-SQL, but one model for solving this problem is to use multiple Junction Tables.

In your case, you would have a table called AddressUsers that has two columns: AddressId and UserID and a table called AddressCompanies that has the columns AddressId and CompanyId.

Answer 3

What you have is a polymorphic association. I'm not familiar with linqtosql, but if it supports referential integrity on this type of relationship, then fear not, do whatever maps.

In standard practice, a polymorphic association can usually be overcome by reversing it. You should go with one intersection (junction) table each for User and Company to join them to Address. This is similar to a many to many relationship, where each row in the intersection table refers to one User and one Address.

If you use the intersection tables, to avoid many-to-many (but keep one-to-many) put a unique constraint on the address key in the intersection tables.

If you're having ORM issues, use a parent address table that joins with UserAddress and CompanyAddress.

Answer 4

Both tpdi and Adam Crossland described what I wanted to say, so we are all on the same track here. I think you ran into the limitations of LINQ to SQL. Other O/RM mappers like NHibernate and Entity Framework 4 could really shine here, because they allow you to define value objects. When using EF for instance, you can define all address fields inside the Users table and inside the Company table (and have fields for payment address and fields for shipping address for instance) and on the .NET side extract those groups of fields into a single Address value object. LINQ to SQL doesn't allow you to do this and basically only allows a one-to-one mapping between your .NET objects and your tables.

Answer 5

I recommend following the advice of tpdi, but use a one to many relation between users/companies and addresses; at least then all your key data types are the same.

The main reason for adding my answer is in response your second suggestion of storing an ObjectID and an ObjectType - attribute splitting is a bad idea, avoid!

Have a read of this Celko post: http://www.tdan.com/view-featured-columns/9852