Does this inheritance design belong in the database?

Question

=== CLARIFICATION ====

The 'answers' older than March are not answers to the question in this post!

Hello

In my domain I need to track allocations of time spent on Activities by resources. There are two general types of Activities of interest - ones base on a Project and ones based on an Account.

The notion of Project and Account have other features totally unrelated to both each other and capturing allocations of time, and each is modeled as a table in the database.

For a given Allocation of time however, it makes sense to not care whether the allocation was made to either a Project or an Account, so an ActivityBase class abstracts away the difference. An ActivityBase is either a ProjectActivity or an AccountingActivity (object model is below).

Back to the database though, there is no little direct value in having tables for ProjectActivity and AccountingActivity, as there is no requirement at this time to report Allocations by either Project or Account. There is one presentation where data is collected and it is useful to present both ProjectActivities and AccountActivities together, and be able to edit a row - to edit the activity some row id is needed, and a unique db Id is useful. The alternative to a db generated Id is one that concatenates the Activity.BusinessId (which for a Project is the Project.Code and for an Account is the Account.Number) along with the Type.

BUT the Allocation table needs to store something in the column for it's ActivityBase. Should that something be the Id of the Project / Account or a reference to tables for ProjectActivity / Accounting? How would the mapping look?

=== Current Db Mapping (Fluent) ====

Below is how the mapping currently looks (this has changed since the first post):

public class ActivityBaseMap : IAutoMappingOverride<ActivityBase>
{

    public void Override(AutoMapping<ActivityBase> mapping)
    {
        mapping.IgnoreProperty(x => x.BusinessId);
        mapping.IgnoreProperty(x => x.Description);
        mapping.IgnoreProperty(x => x.TotalTime);
        mapping.IgnoreProperty(x => x.UniqueId);

        mapping.DiscriminateSubClassesOnColumn("SubClassType");
    }

}

public class AccountingActivityMap : SubclassMap<AccountingActivity>
{
    public AccountingActivityMap() {
        References(x => x.Account);
        DiscriminatorValue("ACCOUNT");
    }
}

public class ProjectActivityMap : SubclassMap<ProjectActivity>
{
    public ProjectActivityMap() {
        References(x => x.Project);
        DiscriminatorValue("PROJECT");
    }
}

There are two odd smells here. Firstly, the inheritance chain adds nothing in the way of properties - it simply adapts Projects and Accounts into a common interface so that either can be used in an Allocation. Secondly, the properties in the ActivityBase interface are redundant to keep in the db, since that information is available in Projects and Accounts.

Cheers,
Berryl

==== Domain =====

public class Allocation : Entity
{
    ...
    public virtual ActivityBase Activity { get; private set; }
    ...
}

public abstract class ActivityBase : Entity
{
    public virtual string BusinessId { get; protected set; }
    public virtual string Description { get; protected set; }
    public virtual ICollection<Allocation> Allocations { get { return _allocations.Values; } }
    public virtual TimeQuantity TotalTime {
        get { return TimeQuantity.Hours(Allocations.Sum(x => x.TimeSpent.Amount)); }
    }
}

public class ProjectActivity : ActivityBase
{
    public virtual Project Project { get; private set; }

    public ProjectActivity(Project project) {
        BusinessId = project.Code.ToString();
        Description = project.Description;
        Project = project;
    }
}

Answer 1

The only thing that jumped out at me was ICollection<> Allocations - that may be a problem.

I know from experience that FNH will automap IList<>, but don't think it can deal with ICollection<>.

If you really need the ICollection behaviour, you might be able to use an Override, but I don't know for sure.

Answer 2

Automapping inheritance in the Fluent NHibernate wiki.

Answer 3

There are several fairly common database design patterns that might apply here. I don’t know enough about the problem domain to give a thorough and complete answer, but the following methods suggest themselves. Hopefully one of these will help, or (as is common with design issues) will lead to informed discussion that eventually produces ideal structures.

The most obvious is to have separate “resource assignment” tables for both Projects and Accounts. You’ve implied that this is not an option, so then we somehow have to merge the two into one entity that will be used/referenced by the assignment table. A simplistic solution is:

-- PROJECT  -----
ProjectId    (primary key)

-- ACCOUNT  -----
AccountId    (primary key)

-- ACTIVITY  ----
ActivityId    (primary key)
ActivityType  (indicates Project or Account)
AssociatedActivityId  (contains either ProjectId or AccountId)

I don’t think much of this, as you cannot enforce referential integrity on the data in AssociatedActivityId. A common alternative would have the third table as:

-- ACTIVITY  ----
ActivityId    (primary key)
ActivityType  (optional but useful, indicates Project or Account)
ProjectId     (nullable, foreign key on Project)
AccountId     (nullable, foreign key on Account)

This also isn’t so hot, as you have to programmatically make sure that one and only one of ProjectId and AccountId is set, and that leaves you with an unused column in every row.

A better if more complex alternative is type/subtype:

-- ACTIVITY  ----
ActivityId    (primary key)
ActivityType  (optional but very useful, indicates Project or Account)

-- PROJECT  -----
ProjectId    (primary key and foreign key on Activity.ActivityId)

-- ACCOUNT  -----
AccountId    (primary key and foreign key on Activity.ActivityId)

You still have the problem of ensuring that a given activity is configured as a Project or Account and not both... but I saw a good trick a while back where you could enforce the subtype in the database like so:

-- ACTIVITY  ----
ActivityId    (Unique value in table)
ActivityType  (NOT optional, "P" or "A" indicates Project or Account)
  and a primary key defined on (ActivityId + ActivityType)

-- PROJECT  -----
ProjectId    (primary key)
TypeProject  (calculated column, always set to “P”)
  and a foreign key on (ProjectId + TypeProject) that references (ActivityId + ActivityType)

-- ACCOUNT  -----
AccountId    (primary key)
TypeAccount  (calculated column, always set to “A”)
  and a foreign key on (AccountId + TypeAccount) that references ActivityId + ActivityType

The advantage of a structure like this is that each activity will reference either a project or an account, never both, you have strong referential integrity, and your allocation table simply references ActivityId. (Plus it wouldn’t be all that hard to add further assignable entities.) The disadvantage is, you now have Project and Account sort of “lumped together” under Activity, which might be great for resource time management but may otherwise improperly associate those entities together.

Again, these are suggestions, and they may or may not suit your problem domain. It still leaves you with the inevitable object/relational impedence issues to resolve, but hey, if programming were easy, all those mooks would be in cubicles next to us and not in Washington.

Addenda

It was late, I overlooked approaching the problem from the other direction:

-- ALLOCATION  ----------
AllocationId  (PrimaryKey)
[Time used, et al.]

-- PROJECT  -----
ProjectId     (primary key)
AllocationId  (foreign key on Allocation)

-- ACCOUNT  -----
AccountId     (primary key)
AllocationId  (foreign key on Allocation)

Yes, you model resource allocation as a separate entity, but since it probably has it’s own attributes (who/what, when, how long), you probably have to do this anyway--in the database, remember. The downside of this model is that every product and account can be assigned at most one allocation. This seems unlikely, which means you’d need:

-- PROJECT_RESOURCE_ALLOCATION  -----
ProjectId     (foreign key on Project)
AllocationId  (foreign key on Allocation)
  and a primary key defined on (ProjectId + AllocationId)

-- ACCOUNT_RESOURCE_ALLOCATION  -----
AccountId     (foreign key on Account)
AllocationId  (foreign key on Allocation)
  and a primary key defined on (AccountId + AllocationId)

i.e. an assocation table linking Allocation with however many entities require allocations. (Yep, ugly table names, I'm just tyring to make it clear what's going on.)