Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Architecture> TablePerEntity and forced mutual exclusivity of subtypes
 

Pages: 1
Architecture
TablePerEntity and forced mutual exclusivity of subtypes
Page:1/1 

  Print all messages in this thread  
Poster Message
Rosacek
User



Location:
CZ
Joined on:
18-Mar-2012 18:02:44
Posted:
133 posts
# Posted on: 09-Nov-2014 23:14:07.  
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

Code:

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)
)

  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8086 posts
# Posted on: 12-Nov-2014 07:05:39.  
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.


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
Rosacek
User



Location:
CZ
Joined on:
18-Mar-2012 18:02:44
Posted:
133 posts
# Posted on: 12-Nov-2014 09:05:40.  
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.
  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.