Hi, I am trying to design DB using "Party" pattern (explained eg here http://martinfowler.com/apsupp/accountability.pdf or in Len Silverston books)

Organization is a subtype of Party. Another subtype in Person, etc.

To make it easy I could simply use just TargetPerEntity entity inheritance as explained in LLBL doc.

But week point of TPE is how to enforce mutual exclusivity of subtypes. If entity is of type Organization, then it has data in Party table and Organization table. But we can easily insert data also into Person table, nothing prevents that. But this way we will violate inheritance rule: "Don't share data in records belonging to supertypes with different subtype instances"

I played with LLBL designer and MSSQL and seems the only way to enforce mutual exclusivity of subtypes is to add some kind of TYPE/CLASS field into SuperType as well as into all SubTypes. In my case I use PartyClassID field, it is FK from PersonClass table.

The trick is to set the default value of PartyClassID in subtype tables, corresponding to subtype class (in fact according to PartyClass, if we define there Organization has ID=1 and Person has ID=1 then we set the same as Default Values into PartyClassID field in SubTypes)

And to create additional relationship between SuperType and Subtype with PartyID+PartyClassID fields, where PartyID+PartyClassID is UniqueKey in SuperType. This is required in order to make secondardy relationship from SubType "Organization".

LLBL designer is happy, TPE is constructed even automatically by designer. Just LLBL shows warning that Organization.PartyClassID is considered as orphaned field, which is perfectly OK as I don't need this field in entity, I need it just in physical Organization table to enforce exclusivity logic.

The only question is if this is good design. The second relation ship just extends the "main relation" between SuperType and Subtype. The main relation is created PK<-->PK&FK. It means referential integrity is ok.

Any comment?

Simplified table design looks like

CREATE TABLE PartyClass (
  PartyClassID int IDENTITY,
  PartyClassName nvarchar(50) NOT NULL,
  CONSTRAINT PK_PartyClass PRIMARY KEY NONCLUSTERED (PartyClassID),
  CONSTRAINT UK_PartyClass UNIQUE CLUSTERED (PartyClassName)
)

CREATE TABLE Party (
  PartyID int NOT NULL,
  PartyClassID int NOT NULL,
  CONSTRAINT PK_Party PRIMARY KEY (PartyID),
  CONSTRAINT UK_Party UNIQUE (PartyID, PartyClassID),
  CONSTRAINT FK_Party_PartyClass FOREIGN KEY (PartyClassID) REFERENCES PartyClass (PartyClassID)
)

CREATE TABLE Organization (
  OrganizationID int NOT NULL,
  PartyClassID int NOT NULL CONSTRAINT DF_OrganizationXXX_PartyClassID DEFAULT (1),
  OrganizationName nvarchar(50) NOT NULL,
  CONSTRAINT PK_Organization PRIMARY KEY (OrganizationID),
  CONSTRAINT UK_Organization UNIQUE (OrganizationID, PartyClassID),
  CONSTRAINT FK_Organization_PartyClass FOREIGN KEY (PartyClassID) REFERENCES PartyClass (PartyClassID),
  CONSTRAINT FK_Organization_Party FOREIGN KEY (OrganizationID) REFERENCES Party (PartyID),
  CONSTRAINT FK_Organization_Party1 FOREIGN KEY (OrganizationID, PartyClassID) REFERENCES Party (PartyID, PartyClassID)
)

IMHO, you don't need PartyClassID to distinguish between subtypes, as that is already done in your tables as per TPE (different types for different subtypes.

You talked about the inheritance rule: "Don't share data in records belonging to supertypes with different subtype instances". You still can do that without any relationship or DB enforcement rule. The dilemma is kind of convention over configuration. If you just code with LLBLGen and enforce your developers to use the tool in the way it should be used, you will never break that rule. If you don't and put a lot of rule enforcement on your DB, it's always possible to insert garbage in the data tables. A middle point is possible, of course.

Yes, you're right. Thanks for comment.

In fact I will import/synchronize some masterdata from other systems by SQL sprocs. If I set rules in DB there will be very little chance to make a mistake in sproc TSQL.