3

One of the programs we're working on is growing and we're at a stage where we can revisit the existing database and spend time 'trying to get it right'. One of the things we're looking at is the data model and how we model 'parties' among other things. We have made do until now with determining customer details from transaction records, but now we want to store more detail and would like to combine the common data we have for staff, suppliers and members, etc.

Each party is either an individual or a group, but can also have one or more roles such as employer, client, member, etc. I realise that various parties in our model don't exist without a relationship to another party (staff need an employer, a customer needs a company to buy from, members need a club, etc) and since I don't want people to add silly relationships like 'employer-client' or 'member-subcontractor', I now have a table called relationship which predefines which roles can go with each other. I haven't seen this before and I wonder, are we on the right track? (Here is a simplified EER diagram to show how we think this might be represented)

Party model EER diagram

I have looked at various documentation on the principles of the party model, but I haven't been able to find any (open source) examples of how it actually looks in pratice. Can anyone recommend a source (preferably in MySQL) or share your experience on how to implement it, perhaps with the table structure or application code?

boatingcow
  • 684
  • 7
  • 19

1 Answers1

2

This SO answer has an implementation of parties.

Another answer, applying this pattern to articles and blog posts and stories has explanations of some of the subtle points in the comments.

Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • Thanks - I've read some of your other answers, so those links are appreciated - especially the tip on the `CHECK` constraints. I take it you don't see anything unusual with my separation of `role` from `party` and making it a characteristic of `relationship` in my diagram above? – boatingcow Sep 05 '11 at 22:19
  • Normally, the requirement for an employer to be an organization and staff to be individuals would start with a table that had one foreign key reference to the organization table ("group", I guess, in your model), and one foreign key reference to the table of individuals. I think that's simpler, easier to understand, easier to maintain, and easier to extend than putting all the relationships between parties into a single table. In fact, it looks like you don't need the tables "individual" and "group" at all, and that's probably not a good thing. – Mike Sherrill 'Cat Recall' Sep 05 '11 at 23:42
  • We're stuck on MySQL for now, so it looks like the CHECK constraints are out and application-level validation is in! Thanks for your comment though - it's made me think about whether we need the `role` table at all, as the application already knows which sub-party to look up depending on the context. Incidentally, it isn't a requirement that an employer be a group and an employee be an individual, etc, but is useful to know if they are one or the other. Individual and group exist alongside API, and the diagram was a simplification of the problem anyway. – boatingcow Sep 07 '11 at 16:23
  • 1
    Whatever relational stuff you have to do at the application level should probably also be tested in an "exception query" once in a while on the server. I run several every night on my server; they check for exceptions to real-world constraints that we couldn't implement in DDL. Not every application will use your app's code. (The MySQL command line, for example.) – Mike Sherrill 'Cat Recall' Sep 07 '11 at 16:44
  • 1
    Your last comment alone is worth 100 textbooks - you don't read things like `"real-world constraints that we couldn't implement in DDL"` in any normal database book! I've marked it as answered. Thanks for your input. – boatingcow Sep 07 '11 at 16:48
  • Well, you have *some* real-world constraints that you're choosing *not* to implement in DDL. "Staff" must be individuals, but you're not referencing the table of individuals. Not *quite* the same thing. – Mike Sherrill 'Cat Recall' Sep 07 '11 at 18:19
  • `Staff` is a subtype of `party`, which in this case is of type `individual`. Since I can't use `CHECK` in MySQL, I have to use application code to enforce this, and to ensure that the same `party_ID` doesn't appear in `group`. Do you see another way to do this in MySQL? On the theme of ensuring a record always exists or always doesn't exist, I've asked a similar question here: http://stackoverflow.com/questions/7337022 – boatingcow Sep 07 '11 at 19:29
  • You'll get better data integrity with a foreign key referencing the table "individuals". Sometimes, you can replace a CHECK constraint with a trigger in MySQL. But triggers aren't absolutely guaranteed to fire. "They are not activated by changes in tables made by APIs that do not transmit SQL statements to the MySQL Server." – Mike Sherrill 'Cat Recall' Sep 07 '11 at 21:39