NHibernate Mapping a Many to Many with Data on Joi

问题:

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.

回答1:

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.

回答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