HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMinor

Party Data Model / Universal Data Model Example

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
exampleuniversalpartydatamodel

Problem

I am trying to implement Party Data Model / Universal Data Model in our application which needs to store different relationships between entities (People, Companies).

Example of relationships: Employer/Employee, Husband/Wife, Seller/Customer.

We also need to store the different types of roles a person can have inside a company.

For example, a person can be Employee at Google, but at the same time occupies a Head of Development position.

I am using this schema as an example, but i need to admit that i don't fully understand it.

From the schema, i think that party role is a tree-structure table? A role can have a parent role? Example: CEO > Head of Development > Developer

Or, party roles are organized in categories, like in the picture? Person Role, Customer, Organization Role.

I have tried to create the DB schema for this picture, and this is what i have:

And the data would look like this

Party                       
John Smith (Person)                     
Google (Organization)                       
Jane Smith (Person)                     

PartyRelationshipType                       
Employment                      
Married                     

PartyRole                       
Employer                        
Employee                        
Husband                     
Wife                        

PartyRelationship                       
Id          RelationshipType            FromParty           ToParty         Role            FromDate            ThruDate
1           Employment                  Google              John Smith      Employer        1/01/2008           16/08/2011
2           Married                     Jane Smith          John Smith      Wife            15/11/2000


Is the structure ok?

How can i define the relationships between Google employess for example? All of them they are employees, but at the same time they occupy different titles. Should i create a new Role for each title? or this data should be saved somewhere else?

Solution

I think you need to step back first and familiarise yourself with the notation, which seems to be Barker:

http://www.agiledata.org/essays/dataModeling101.html

The Party Role structure does illustrate a hierarchy of a type, but each of the subtypes uses the same primary key, the Party Role ID.
Presumably, when implemented each of these entities would have varying attributes, so wouldn't be implemented as a single PartyRole table. Also, there will be business rules of what other tables the party roles could relate to.
But in terms of Party Relationships, what you have seems correct

Context

StackExchange Database Administrators Q#105875, answer score: 2

Revisions (0)

No revisions yet.