NHibernate Mapping 1 Object To 2 Tables

0
votes

We want to use NHibernate as our persistence layer in our application. We are also using Fluent NHibernate for the mappings.

We get Person data from a 3rd party and we need to save the data to our database. It works better in the code to have all properties on one object, but it makes more sense to save the data to 2 tables in our database.

Our object looks like this:

public class Person
{
    public virtual long VersionNumber { get; set; }
    public virtual string FirstName { get; set; }
    public virtual string LastName { get; set; }
    public virtual string IdentificationNumber { get; set; }
}

Our database tables look like this:

CREATE TABLE PersonVersion (
    [PK] VersionNumber bigint NOT NULL,
    [FK] PersonDemographicsId int NOT NULL
)
CREATE TABLE PersonDemographics (
    [PK] PersonDemographicsId int NOT NULL,
    IdentificationNumber nvarchar(9) NOT NULL,
    FirstName nvarchar(100) NOT NULL,
    LastName nvarchar(100) NOT NULL
)

When we receive new data, the version number can change, but the rest of the demographics could be the same. What we need NHibernate to do is save a new record to the PersonVersion table which links to the existing PersonDemographics record. If the demographics have changed, then we will create a new record in both tables. But most of the time, once we've downloaded the initial data, the demographics won't change as often as the version number. We need to keep track of all version numbers so that's why it's necessary to create a new PersonVersion record.

How would we accomplish this using NHibernate and mappings using Fluent NHibernate?

Also, as you can see, our Person object currently does not have a PersonDemographicsId because it is not needed by our application at all; it is just an ID for the table relationship which is needed in the database. In order to properly map this in NHibernate, do we have to add a PersonDemographicsId property on our Person object?

Thanks for the help!

2
c#.netnhibernateormfluent-nhibernate
Unless PersonDemographics has some heavy BLOB data that you're not showing, I suggest that you consider flattening the version number into that table. The cost of joining and/or subquerying on every request plus the complexity of rebuilding historical states (I can't even see how that will be possible with the model you described) is likely to outweigh any small benefit that you get by persisting less data. - Jay
Yes, I did leave out some properties, but all are simple types; no BLOB data. The reason we've decided to go with this model is because the amount of records we'll be dealing with. Each year, we will have anywhere from 600,000 to 1,000,000 unique persons in our database and each person will have multiple versions throughout the year. Most of the time demographics will not change, but sometimes they will. We will have A TON of duplicated data if we flatten these tables into 1. And from a storage point of view, it makes no sense to continually duplicate the demographics just for version changes. - Blang
possible duplicate of Fluent NHibernate - Map 2 tables to one class - luvieere

2 Answers

2
votes

This article http://ayende.com/blog/2327/multi-table-entities-in-nhibernate explains a way to map a single class to two tables in the database.

0
votes

just an idea, maybe has to be tweaked

public class Person
{
    public virtual int Id { get; set; }

    internal protected virtual IList<long> VersionNumbers { get; set; }
    public virtual long VersionNumber {
       get { return VersionNumbers[VersionNumbers.Count - 1]; }
       set { VersionNumbers.Add(value); }
    }
    public virtual string FirstName { get; set; }
    public virtual string LastName { get; set; }
    public virtual string IdentificationNumber { get; set; }
}

public class PersonMap : ClassMap<Person>
{
    public PersonMap()
    {
        Table("PersonDemographics");
        Id(p => p.Id, "PersonDemographicsId").GeneratedBy.Assigned();
        Map(p => p.FirstName);
        Map(p => p.LastName);
        Map(p => p.IdentificationNumber);

        HasMany(p => p.VersionRecord)
            .Table("PersonVersion")
            .KeyColumn("PersonDemographicsId")
            .Element("VersionNumber")
            .OrderBy("VersionNumber")
            .Cascade.All();
    }
}