Data schema for complex entities - which approach is simpler

Question

I am reviewing a database model. I need some help with a part of the model. At this stage I'm just concerned with the logical model, not the implementation. I want to adopt a best practice.

Summary of problem

• The database is used by an app that manages legal cases for a law firm.

• Each case has multiple parties. (By party, I mean a some legal enity in the real world that has a stake in the case.)

• There are about 40 different types of parties.

• 2 of these types can be a single person, a single organisation, or multiple persons and/or organisations joined together in any combination.

• The other 38 types can be either a single person or a single organisation.

• Each case always has 2 parties of the complex types (i.e. potentially a combination of persons and organisations).

• Usually there are 5 to 10 parties in total per case.

Options

1. Each party is modelled as potentially a combintation of any number of persons and organisations. The tables would look like this:

   • Case <- CasePartyAssignment -> Party
     Where all parties are potentially a combination of persons and organisations:
   • Party <- PartyPersonAssignment -> Person
   • Party <- PartyOrgAssignment -> Organisation

2. Alternatively, I model this with 3 different types CasePartyAssignment tables.

   The first is the same as 1 above, which covers the complex scenario:

   • Case <- CaseComplexPartyAssignment -> ComplexParty

     In addition I add specific tables for the simple scenarios:

   • Case <- CasePersonAssignment -> Person

   • Case <- CaseOrgAssignment -> Organisation

The way I see it, both options have advantages and disadvantages. For example, in option 1 I create a single way to store the data, which in itself is simple due to consistency. But that means I also model a party that I know is a simple Person using the PartyPersonAssignment designed to model the complex party.

Does anyone have any commnts/opinions about these options?

Answer 1

I think option 1 is more flexible. With option 1, you'd be able to add or remove Persons or Organizations from a Party by adding or removing records from the many-to-many tables, whereas with option 2, if you start out with a simple single Person or single Organization setup, it is a little more clunky to modify it into a ComplexParty. I guess I prefer to keep corner cases out of the data model, and try to just use a flexible model that can handle the corner cases in the same way as the more complex cases.

I don't think it's too bad to represent the single entity cases as a Party, even though the party is unnecessary in that case.

Answer 2

I would model it a bit differently:

• Person
• Organisation
• PerOrg: super-type of Person/Organisation, i.e. a LegalEntity
• Party: either a complex party or a simple party
• PerOrgPartyAssignment: the relationship between PerOrg and Party, you can have either one or many PerOrgs per Party (but a minimum of one)
• Case: here you only have PlantiffParty and DefendentParty

The PerOrg concept (aka 'Anyone'/'Legal Entity'/'Party'...) is quite a powerful concept for any data models involving commercial relationships. I've used it a number of times and it always end up simplifying the data model when there are seemingly complex relationships.