1

I have a User table and an Address table. They are connected by a join table. The mapping for that is straight forward, but I have some data on the join table that I would like to show up on the Address table.

There may be a better way to set this up also, which I'm open to suggestions for.

Here is the table structure.

CREATE TABLE [dbo].[User]
(
    [Id] INT NOT NULL IDENTITY PRIMARY KEY,
    ...
)

CREATE TABLE [dbo].[Address]
(
    [Id] INT NOT NULL IDENTITY PRIMARY KEY,
    ...
)

CREATE TABLE [dbo].[AddressType]
(
    [Id] INT NOT NULL IDENTITY PRIMARY KEY,
    [Name] NVARCHAR( 10 ) NOT NULL, -- Values: 'Shipping', 'Billing'
    ...
)

CREATE TABLE [dbo].[UserAddress]
(
    [UserId] INT NOT NULL FOREIGN KEY REFERENCES [dbo].[User]( [Id] ),
    [AddressId] INT NOT NULL FOREIGN KEY REFERENCES [dbo].[Address]( [Id] ),
    [AddressTypeId] INT NOT NULL FOREIGN KEY REFERENCES [dbo].[AddressType]( [Id] ),
    ...
)

What I want is to have a list of shipping and billing addresses on the user object. How would I map that? I'm using Fluent NHibernate for mapping.

I originally started out with two join tables, BillingAddress and ShippingAddress, that were just joins between the User and Address tables. This would work fine, but then there are 2 tables with the exact same structure that do the same thing, and it just didn't seem right.

Josh Close
  • 22,935
  • 13
  • 92
  • 140

2 Answers2

3

If your join table (e.g. UserAddress) is more than just a pair of foreign keys and has metadata (e.g. AddressType) in it, you need to map the association as a pair of one-to-many relationships and represent the association as an entity in your object model. For example:

User <has-many> AddressAssociation <references> Address

AddressAssociation would have the AddressType as a property. (Basically I'm renaming UserAddress to AddressAssociation to make it sound more like a domain entity.) User to AddressAssociation is a one-to-many from User->AddressAssociation. The references is a many-to-one between AddressAssociation and Address.

James Kovacs
  • 11,549
  • 40
  • 44
  • If there is no way to map directly and have the meta data put into the address object, then maybe having 2 association tables is the way to go. Only problem is, what if there ends up being a lot more AddressTypes added. I don't think that will happen in this case, but possibly in another. – Josh Close Nov 16 '10 at 21:36
  • If it isn't likely to add more address types, then I would map it using two tables as you did originally. You can always refactor your schema later and write migration scripts to move the data into the new structure. I did that frequently in my last project as requirements changed (or better understood) and it wasn't a huge issue. As with anything your mileage may vary. – James Kovacs Nov 17 '10 at 00:11
2

The best solution is to use two separate tables. I see no real reason why it wouldn't "seem" right.

Now, if you really, really want to make things more complicated, have a look at mapping multiple sets in one table in hibernate

Community
  • 1
  • 1
Diego Mijelshon
  • 52,548
  • 16
  • 116
  • 154