A party is an "abstraction" for partys, not the one you vent to last night, but hopefully several parties where present.
A person is a party, and so is an organization. In my capacity as a developer I could be said to have a role as a developer, and when I get sick and visit the doctor I have a role as a patient. When one doctor stops being my doctor, his role as my doctor ends, but that does not alter the fact that he has been my doctor - something that may prove to be information of impotance should complications arise from previous cases. As I change doctors a new person gets the role as my doctor.
Here is something similar, and more comprehensive, to the design I'm outlining:
Picture of a similar model[
^]
A Universal Data Model For Relationship Development[
^]
In a "real" design we could have entities for PartyType, Party, RelationShipType, RelationShipRoleAssignment, RelationShip ,ContactMechanismType, ContactMechanism, CaseType, Case, and many more. As a person I would have several contactmechanisms assigned to me, and as this information usually changes over time, they too should have FromTime/ThroughTime fields.
In a medical system I would be part of a relationship between the medical facility and me in my role as patient, and medical personel enters and leaves that relationship in their various roles working on cases. If I worked as a doctor at our medical facility, I could sometimes have the role as patient too - but I should not be able to have the role as my own doctor.
General very simplified structure for dealing with interactions between parties, like for instance doctors and patients - but hopefully enough to get my drift. It's a very simplified subset of the model proposed by Len Silverston.
CREATE SCHEMA [Party]
GO
CREATE SCHEMA [Case]
GO
CREATE TABLE [Party].[PartyType]
(
[Id] bigint NOT NULL,
[Name] nvarchar(255) NOT NULL,
[Extends] bigint,
[Abstract] bit NOT NULL,
[Description] nvarchar(2028),
CONSTRAINT PK_PartyType PRIMARY KEY([Id]),
CONSTRAINT FK_PartyType_Extends_To_PartyType_Id FOREIGN KEY([Extends]) REFERENCES [Party].[PartyType]([Id]),
CONSTRAINT UNQ_PartyType_Name UNIQUE(Name)
)
GO
CREATE TABLE [Party].[Party]
(
[Id] bigint IDENTITY NOT NULL,
[Type] bigint NOT NULL,
CONSTRAINT PK_Party PRIMARY KEY(Id),
CONSTRAINT FK_Party_Type_To_PartyType_Id FOREIGN KEY([Type]) REFERENCES [Party].[PartyType]([Id])
)
GO
CREATE TABLE [Party].[Organization]
(
[Id] bigint NOT NULL,
CONSTRAINT PK_Organization PRIMARY KEY(Id),
CONSTRAINT FK_Organization_Id_To_Party_Id FOREIGN KEY([Id]) REFERENCES [Party].[Party]([Id])
)
GO
CREATE TABLE [Party].[Person]
(
[Id] bigint NOT NULL,
CONSTRAINT PK_Person PRIMARY KEY(Id),
CONSTRAINT FK_Person_Id_To_Party_Id FOREIGN KEY([Id]) REFERENCES [Party].[Party]([Id])
)
GO
CREATE TABLE [Party].[Unit]
(
[Id] bigint NOT NULL,
CONSTRAINT PK_Unit PRIMARY KEY(Id),
CONSTRAINT FK_Unit_Id_To_Party_Id FOREIGN KEY([Id]) REFERENCES [Party].[Party]([Id])
)
GO
CREATE TABLE [Party].[PartyRoleType]
(
[Id] bigint NOT NULL,
[Name] nvarchar(255) NOT NULL,
[Extends] bigint,
[Abstract] bit NOT NULL,
[Description] nvarchar(2028),
[ValidForPartyType] bigint NOT NULL DEFAULT 1,
CONSTRAINT PK_PartyRoleType PRIMARY KEY([Id]),
CONSTRAINT FK_PartyRoleType_Extends_To_PartyRoleType_Id FOREIGN KEY([Extends]) REFERENCES [Party].[PartyRoleType]([Id]),
CONSTRAINT FK_PartyRoleType_ValidForPartyType_To_PartyType_Id FOREIGN KEY([ValidForPartyType]) REFERENCES [Party].[PartyType]([Id]),
CONSTRAINT UNQ_PartyRoleType_Name UNIQUE(Name)
)
GO
CREATE TABLE [Party].[PartyRole]
(
[Id] bigint IDENTITY NOT NULL,
[Type] bigint NOT NULL,
[Party] bigint NOT NULL,
[FromTime] datetime2 NOT NULL,
[ThroughTime] datetime2,
CONSTRAINT PK_PartyRole PRIMARY KEY(Id),
CONSTRAINT FK_PartyRole_Type_To_PartyRoleType_Id FOREIGN KEY([Type]) REFERENCES [Party].[PartyRoleType]([Id]),
CONSTRAINT FK_PartyRole_Party_To_Party_Id FOREIGN KEY([Party]) REFERENCES [Party].[Party]([Id])
)
GO
CREATE TABLE [Case].[CaseType]
(
[Id] bigint NOT NULL,
[Name] nvarchar(255) NOT NULL,
[Extends] bigint,
[Abstract] bit NOT NULL,
[Description] nvarchar(2028),
CONSTRAINT PK_CaseType PRIMARY KEY([Id]),
CONSTRAINT FK_CaseType_Extends_To_CaseType_Id FOREIGN KEY([Extends]) REFERENCES [Case].[CaseType]([Id]),
CONSTRAINT UNQ_CaseType_Name UNIQUE(Name)
)
GO
CREATE TABLE [Case].[Case]
(
[Id] bigint IDENTITY NOT NULL,
[Type] bigint NOT NULL,
CONSTRAINT PK_Case PRIMARY KEY(Id),
CONSTRAINT FK_Case_Type_To_CaseType_Id FOREIGN KEY([Type]) REFERENCES [Case].[CaseType]([Id])
)
GO
CREATE TABLE [Party].[PartyToCaseRole]
(
[Id] bigint NOT NULL,
[Case] bigint NOT NULL,
CONSTRAINT PK_PartyToCaseRole PRIMARY KEY(Id),
CONSTRAINT FK_PartyToCaseRole_To_PartyRole_Id FOREIGN KEY([Id]) REFERENCES [Party].[PartyRole]([Id])
CONSTRAINT FK_PartyToCaseRole_To_Case_Id FOREIGN KEY([Id]) REFERENCES [Case].[Case]([Id])
)
GO
The above is quite simplified, but I still hope it will point you in the right direction. Information about names, adresses, contact mechanisms and so on are temporal in nature and should be factored into separate tables to allow you to track changes - NOT OVERWRITE THEM!!
When a doctor stops having a role as a doctor you set the ThroughTime, when a doctor starts being a doctor you set the FromTime.
While designing the tables, it's a good idea to keep in mind that you may eventually wish to map them to something outside the db - perhaps c#.
The follwing outlines something that could form the basis for a suitable structure:
public abstract class Identified
{
public long Id {get;set;}
}
public abstract class Named : Identified
{
public string Name { get; set; }
}
public abstract class abstract Type : Named
{
public long? Extends {get;set;}
public bool Abstract {get;set;}
public string Description {get;set;}
}
class PartyType : Type
{
}
public abstract class Party : Identified
{
public PartyType {get;set;}
List<partyrole> Roles {get;}
}
public abstract class FormalParty : Party
{
}
public abstract class InformalParty : Party
{
}
public abstract class Person : FormalParty
{
}
public abstract class Oragnization : FormalParty
{
}
public class Unit : InformalParty
{
}
public class PartyRoleType : Type
{
}
public class PartyRole : Identified
{
public PartyRoleType {get;set;}
public Party Party {get;set;}
public DateTime FromTime {get;set;}
public DateTime ThroughTime {get;set;}
}
public class CaseType : Type
{
}
public class Case : Identified
{
CaseType CaseType {get;set;}
}
public class PartyCaseRole : PartyRole
{
Case Case {get;set;}
}
</partyrole>
I hope the outline for a possible c# implmentation helps to clarify some of the database design.
Obviously this doesn't come close to an actual design for a "real" system, it doesn't even satisfy the brief desciption i gave at the top - but I hope it serves to illustrate some "real-world" "problems" and how you may design you system to cope with them - specifically that most things are temporal in nature - needs FromTime/ThroughTime functionality.
If you work your way through the implications of this design, you'll end up with a database structure that initially will seem quite, even very :), complicated. The good thing is that it will usually be quite easy to work with, as you have a structure that is capable of dealing with real scenarios through simple mechanisms.
Regards
Espen Harlinn
Submit an article or tip