Mapping a class with multiple collection properties containing the same object type

Question

Our system generates emails with user-chosen lists of To / CC / BCC contacts. I wanted to store them as follows in our SQL Server database, here's the simplified database table structure:

CREATE TABLE [Contact] (
    [ContactID] [int] IDENTITY (1, 1) NOT NULL,
    [Name] [varchar] (100) NOT NULL,
    [EmailAddress] [varchar] (100) NOT NULL,
    CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED ([ContactID])
)

CREATE TABLE [Email] (
    [EmailID] [int] IDENTITY (1, 1) NOT NULL,
    [Subject] [varchar] (500) NOT NULL,
    [Message] [text] NULL,
    [DateSent] [datetime] NOT NULL,
    CONSTRAINT [PK_Email] PRIMARY KEY CLUSTERED ([EmailID])
)

CREATE TABLE [EmailContact] (
    [EmailID] [int] NOT NULL,
    [ContactID] [int] NOT NULL,
    [Type] [varchar] (4) NOT NULL,
    CONSTRAINT [PK_EmailContactList] PRIMARY KEY CLUSTERED 
    (
     [EmailID],
     [ContactID],
     [Type]
    ),
    CONSTRAINT [FK_EmailContact_Contact] FOREIGN KEY ([ContactID]) REFERENCES [Contact] ([ContactID]),
    CONSTRAINT [FK_EmailContact_Email] FOREIGN KEY ([EmailID]) REFERENCES [Email] ([EmailID])
)

This to me looks like a case of a many-to-many relationship between the Email and Contact objects. However, I would like the Email domain object to have 3 separate IList properties for the contacts of each list (To / CC / BCC) such that I could enable the following code to work :

testEmail.ToContacts.Add(contact1)
testEmail.CCContacts.Add(contact2)
testEmail.BCCContacts.Add(contact3)

Can this be done without adding an additional domain object (EmailContact)? Do I need to use two many-to-one relationships with an additional domain object like Billy McCafferty mentions here?

Also, how would I represent this my NHibernate mapping file?

Answer 1

According to the article you cite by Billy McCafferty:

At first glance, this might imply that you'd have to create a CustomerAddress object in your domain model to reflect this relationship table in the DB; that's not the case with NHibernate. Instead, you'd simply add a many-to-many association to the Customer.hbm.xml which indicates that the CustomerAddresses table should be used as the lookup for managing this many-to-many relationship.

Replace McCafferty's "CustomerAddresses" example with your "EmailContacts". According to the article, you WILL need an "EmailContacts" table to represent the many-to-many relationship between emails and contacts, but not an EmailContacts domain object. If you want three separate relationships for To, CC, and BCC, create three join tables: To_EmailAddresses, CC_EmailAddresses, and BCC_EmailAddresses.

I'm not totally familiar with NHibernate so I don't know exactly how to update the mapping file, but the article indicates that the file relating to your Email domain object (Email.hbm.xml) would be the one to update. The NHibernate documentation should tell you how to represent a many-to-many relationship in a mapping file.

Answer 2

I didn't get an answer here, so I also posted to NHUsers instead, and I got 2 suggestions - I think we're going with Jon Stelly's idea.