domain inheritance to relational db - looking for a working db model

Question

Hello:

The focus of this question is primarily to develop a relational db scheme given the object model here.

This part of the system is concerned with time based allocations made by Resources (different flavors of Employees & Contractors) to Activities (either a Project or an Account). Data entry is weekly, and the domain model must validate the weekly entries (~ 5 per resource with 200 resources) before letting them be persisted. Queries from the db must support approval / analysis of Allocations both by activity(ies) and resource(s).

I am already using NHib (& FNH) in simpler parts of the system, so ancillary to my primary focus is to flesh out other concessions the domain model must make for the sake of persistence with these tools. For example:
1) properties that would otherwise not be virtual are virtual
2) id generation (Entity as a base class is automapped to generate an ID (int)

Here are domain classes of interest, which are tested for presentation purposes:

 public class Allocation : Entity {
    public virtual ResourceBase Resource { get; private set; }
    public virtual ActivityBase Activity { get; private set; }
  public virtual TimeQuantity TimeSpent { get  private set; }
}

public abstract class AllocatableBase : Entity {
    protected readonly IDictionary<DateTime, Allocation> _allocations = new SortedList<DateTime, Allocation>();

    public virtual IEnumerable<Allocation> Allocations { get { return _allocations.Values; } }

}

public abstract class ResourceBase : AllocatableBase {
    public virtual  string Name { get; protected set; }
    public virtual  string BusinessId { get; protected set; }
    public virtual  string OrganizationName { get; protected set; }
}

// example of a concrete ResourceBase
public sealed class StaffMemberResource : ResourceBase  {
    public StaffMember StaffMember { get; private set; }

    public StaffMemberResource(StaffMember staffMember)
    {
        Check.RequireNotNull<StaffMember>(staffMember);
        UniqueId = GetType().Name + " " + staffMember.Number; // bad smell here
        Name = staffMember.Name.ToString();
        BusinessId = staffMember.Number;
        OrganizationName = staffMember.Department.Name;
        StaffMember = staffMember;
    }
}


public abstract class ActivityBase : AllocatableBase {

    public virtual void ClockIn(DateTime eventDate, ResourceBase resource, TimeQuantity timeSpent) {
        ... add to the set of allocations if valid
    }

    public virtual string Description { get; protected set; }
    public virtual string BusinessId { get; protected set; }
}

// example of a concrete ActivityBase
public sealed class ProjectActivity : ActivityBase {
    public Project Project { get; private set; }

    public ProjectActivity(Project project) {
        Check.RequireNotNull<Project>(project);
        Description = project.Description;
        BusinessId = project.Code.ToString();
        UniqueId = GetType().Name + " " + BusinessId;
        Project = project;
    }
}

Here is the initial db structure I'm throwing out, looking for feedback here:

table Allocations (
   AllocationID INT IDENTITY NOT NULL,
   StartTime DATETIME NOT NULL,
   EndTime DATETIME NOT NULL,
   primary key (AllocationID)

      foreign key (ActivityID) 
      foreign key (ResourceID)

)

table Activities (
   ActivityID INT IDENTITY NOT NULL,
   primary key (ActivityID)

   ActivityType NVARCHAR(50) NOT NULL,

      foreign key (WrappedActivityID) // for lack of a better name right now

)

table Projects (    // example of a WrappedActivity
    ProjectID INT IDENTITY NOT NULL,
   Prefix NVARCHAR(50) null,
   Midfix NVARCHAR(50) null,
   Sequence NVARCHAR(50) null,
   Description NVARCHAR(50) null,
   primary key (ProjectID)
)

table Resources (
   ResourceID INT IDENTITY NOT NULL,
   primary key (ResourceID)

   ResourceType NVARCHAR(50) NOT NULL,

      foreign key (WrappedResourceID)

)

table FullTimeStaffMembers (  // example of a WrappedResource
    StaffMemberID INT IDENTITY NOT NULL,
   Number NVARCHAR(50) not null unique,
   FirstName NVARCHAR(50) not null,
   LastName NVARCHAR(50) not null,
   DepartmentID INT not null,
   primary key (StaffMemberID)
)

I'm not shooting for a specific inheritance mapping scheme just yet (ie, table per class, etc) as much as looking to get a starting db model that works (even if not optimally performance wise). I'm already painfully aware that db skills make my programming skills look awesome by comparison, so all constructive feedback & questions welcome!

Cheers,
Berryl

Answer 1

I am confused by your table structure. You mention foreign keys, but you don't say what table the foreign key references. In most cases, I can infer the table based on your naming conventions. If you create the tables using SQL, you will do well to make the references explicit as to table. In most cases, you will also do well to enforce referential integrity in the schema.

But I don't understand the connection between WrappedResourceID and ProjectID. I'm guessing that the following design pattern will help you to model different kinds of activity that all have some common attributes, but also have attributes peculiar to the type of activity.

Do a web search on "generalization specialization relational model". You'll get some good articles describing this pattern. It's a pattern that is generally not taught in beginning db design courses, but people accustomed to inheritance need to know it.