Many-to-many mapping with extra columns in join ta

问题:

Here is the domain that I wish to have:

public class Person
{
    public int Id { get; set; }
    public IList<AcquiredCertificate> AcquiredCertificates { get; set; }
}

public class AcquiredCertificate
{
    public Person Acquirer { get; set; }
    public Certificate Certificate { get; set; }
    public DateTime DateAcquired;
}

public class Certificate
{
    public int Id { get; set; }     
}

And this is the schema that I have:

CREATE TABLE People (
    PersonId INT PRIMARY KEY
);

CREATE TABLE Certificates (
    CertificateId INT PRIMARY KEY
);

CREATE TABLE CertificatesAcquiredByPeople (
    PersonId INT,
    CertificatedId INT,
    DateAcquired DATETIME
);

It's a contrived schema and domain but it's pretty much the same as something that I am working with. I currently have it working by writing a 3rd domain entity to represent the CertificatesAcquiredByPeople table but that really seems strange to me.

How would I map this using NHibernate? I believe the component tag in the hbm file should do what I want, but I can't quite figure it out.

Is my domain out of whack because I have a DateAcquired property on my Certificate class? The date really is only a concern of a Person that has a certificate.

[Edit]

I've altered the domain model now to reflect that a new entity is needed. Now for the mapping do I need 3 (for each entity) mappings or can I do it with 2 (for Person and Certificate)?

回答1:

By design, NHibernate only supports the implicit many-to-many mapping if there is absolutely NOTHING other than the pair of FKs represented in the intermediate (middle) table that holds the many-to-many relationship.

Some time ago, Billy McCafferty blogged about this exact 'issue' (not really an issue since its BY DESIGN)...

http://devlicio.us/blogs/billy_mccafferty/archive/2008/07/11/when-to-use-many-to-one-s-vs-many-to-many-with-nhibernate.aspx

回答2:

I think you need 3, if you are ever going to get at the DateTime value.

回答3:

Your implementation is exactly correct. Your join table includes the two key fields (making a composite primary key for the table) and the datetime field is superfluous to that. It is in fact an extra attribute on the join table and for that you need an entity.

On a UML class diagram it would show up as an attribute on the join too.

回答4:

I would rename CertificatesAcquiredByPeople something
like CertificatesAcquiredEvent (it implies that there is more than one key and a datetime)

I agree it needs to be a seperate Entity as far as NHibernate is concerned.

回答5:

Re: your updated Q, you will need three mappings, one for each of the three entities that now participate in the pair of one-to-many relations.