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?
Can somebody show me a real-data example?
Best Answer
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