0

Supposed I have 4 tables defined as follows:

Projects: [ Project_Id, Project_Name ]

Vendors: [ Vendor_Id, Vendor_Name ]

Project_Vendors: [ Project_Vendor_Id, Vendor_Id, Project_Id ]

Payments: [ Payment_Id, Project_Vendor_Id, Payment_Amount ]

The relationships should be as follows:

  • A Project can have many Vendors associated
  • A Vendor can belong to many projects
  • Many payments can be made to many vendors of a project (so what makes a payment unique is a project AND a vendor)

I can easily map Project and Vendor objects and their relation using the ProjectVendors lookup table with the following:

public class ProjectMapping : ClassMap<Project>
{
    public ProjectMapping()
    {
         Table("Projects");
         Id(x => x.Id).Column("Project_Id").GeneratedBy.TriggerIdentity();
         HasManyToMany(x => x.Vendors).Table("Project_Vendors")
             .ParentKeyColumn("Project_Id")
             .ChildKeyColumn("Vendor_Id")
             .Cascade.AllDeleteOrphan();
    }
}

My question is, if my classes are defined as follows:

public class Payment
{
    public virtual int? Id { get; set; }
    public virtual Project Project { get; set; }
    public virtual Vendor Vendor { get; set; }
    public virtual double Amount { get; set; }
}

public class Project
{
    public virtual int? Id { get; set; }
    public virtual string Name { get; set; }
    public virtual List<Vendor> Vendors { get; set; }
    public virtual List<Payment> VendorPayments { get; set; }
}

How can I map my Project and Payment objects using the lookup table Project_Vendors?

Christopher.Cubells
  • 911
  • 10
  • 26

1 Answers1

1

Not directly possible without mapping the Project_Vendors table because NHibernate has to know where the id to reference comes from. You might be able to fake it but i would advice against it.

class ProjectVendorPair
{
    public virtual int Id { get; set; } 
    public virtual Project Project { get; set; }
    public virtual Vendor Vendor { get; set; }
}

// in PaymentMap
References(x => x.Owner, "Project_Vendor_Id");

Or you change the database Structure:

  • get rid of column Project_Vendor_Id
  • have columns Project_Id and Vendor_Id in Payment table
Firo
  • 30,626
  • 4
  • 55
  • 94
  • Yeah I kind of figured this. I don't mind changing the database structure except that it takes away from the integrity of the data a bit and also creates a small amount of duplicate data. Would you consider this approach acceptable in enterprise level software where the integrity of your data is more reliant on an application level than on a database level? – Christopher.Cubells Dec 11 '12 at 21:39
  • dataintegrity is still secure when defining a unique constraint over `Project_id` and `Vendor_id` in link table and a foreignkey constraint from `Payment.Project_id/Vendor_id` to Link table. It might even be more efficient since you do not have to go to the database to get the `Project_Vendor_Id` when making a payment – Firo Dec 12 '12 at 11:41