Trying to come up with a compound entity data structure

[Tom R.]

Hi,

For my solution I have set up a party model, with separate tables for People, Organizations, and Parties. Since a Party can be either a person or an organization, the user creates a new party record, selecting whether it's a person or org; then a related child record is created in the appropriate table. 

My question is, I would like to come up with some sort of compound party model, but am not sure how to structure it. In my solution, Parties can own interests in leases, and they get credited with line items in a line items table. So for example I might have Bill Smith and Jean Smith with separate records in the Party table (and associated records in the People table). I want to be able to create a compound party out of them, based on their relation as husband and wife; so the party would be "Bill Smith and Jean Smith, husband and wife". I would like the new party record to be based on the existing records in the Parties and People tables; not a completely new record based on an Organization. Similar examples might be "Jennifer Jones, Mike Carlson and Jeff Black, co-trustees of the Jones Family Revocable Trust", where each of the individual people/organizations, are existing records already.

I'm not sure whether I might be able to do something with repeating fields, or if I need an additional join table, or what. Any advice appreciated.

Thanks,

Tom

[comment]

33 minutes ago, Tom R. said:

Parties can own interests in leases, and they get credited with line items in a line items table.

How does this lead to the need to group several parties in a "compound party"?

In any case, if a party can belong to more than one such group and you need to record any attributes of such association (e.g. role or percentage), you will need a join table.

[Tom R.]

Sorry, I guess that was more of an aside than a because. 

A join table between two occurrences of the Parties table? Or between the Parties table and...?

I will play around with it and see what I can figure out.

Thanks!

[comment]

2 hours ago, Tom R. said:

A join table between two occurrences of the Parties table? Or between the Parties table and...?

If you want the group to be a Party of its own, then of course the join table will be between two occurrences of the Parties table.