0

My application - based on a relational database - has to deal with two kinds of customer entities, (natural) persons and organizations. They have different attributes and therefore each group lives in its own table.

Now I have other data objects like addresses or tags or memos. Each record belongs to exactly either a person or an organization.

I wonder how to represent that connection. When linking to a person or an organization I don't want to have another field with the target table.

I was thinking about creating a kind of super-entity on top of persons and organizations. I've seen this in CRM apps like Highrise or Capsule CRM - they have called it "parties". Every person and every organization has a corresponding entry in the party table. Other tables now link to the party table instead of directly to persons or organizations.

Party:

  id | person_id | org_id
   1 |         1 |
   2 |           |      1

In the above example the party record #1 links to person #1 and party #2 links to organization #1.

Is this really a feasible way or am I just a bit to blind for a simpler solution?

Oliver Jakoubek
  • 416
  • 1
  • 3
  • 6

3 Answers3

1

I'd use the approach with the "super" table but turn the reference around. person and organizations will reference the party table, not the other way round.

That is the usual design pattern to model "inheritance" in a relational database if the DBMS doesn't support it directly (like e.g. PostgreSQL)

 party (id, customer_number, [other common attributes] )
 person (person_id, party_id, ... )
 organizations (org_id, party_id, ... )
 address (address_id, party_id, ...)

For convenience you can create views that join party/person and party/organizations so that you can easily access the base attributes together with the specialized type.

  • That is a quite simple solution. As a side-effect it moves the "main" tables for both entities down to the same level like addresses etc. and making the head data only another aspect of an party. Would you create a type field (P/O) in the party table? – Oliver Jakoubek Apr 22 '11 at 15:34
  • No, with the two separate "type tables" there is no need for a type column. The only possible use would be that you can quickly count how many persons and organizations you have (without doing two separate counts on the child tables). Apart from that I cannot see any advantage of the type column. –  Apr 22 '11 at 15:40
1

I've answered similar questions several times on SO. The two most informative answers from me are here and here. Both those answers use similar structures in different contexts; the comments for the second one are particularly helpful, I think.

Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • I like the idea to use the party table's PK as a common ID for parties and the subtype entries. This avoids to store the (different) subtype ID in the party table. – Oliver Jakoubek Apr 22 '11 at 15:38
  • Nice answers. I especially like the way you solved the problem how to make sure the correct type is referenced using the check constraints. But wouldn't a type column as part of the primary key in the base table be more reliable? –  Apr 22 '11 at 15:42
  • At the SQL level *in this context*, it doesn't really matter whether a) the primary key is the id and the unique constraint is on the combination of id and type, or b) the primary key is the combination of id and type and the unique constraint is on the id. A foreign key constraint can target either the PK or the unique constraint, regardless of which way they're defined. What matters is that, for safety and flexibility, you need both of those constraints. – Mike Sherrill 'Cat Recall' Apr 22 '11 at 16:28
  • Oh, and I can't take credit for *solving* the problem. I learned this stuff through Usenet newsgroups years ago. (Hmm. Maybe decades ago.) I don't remember who I learned it from, but if I can figure out who it was, I'll happily give him credit. – Mike Sherrill 'Cat Recall' Apr 22 '11 at 16:29
0

Having a meta table like "Party" is one option, although I would make the solution a little more extensible. How about this

PartyType
- id
- name

Party
- id
- partyTypeId
- typeSpecificId

In the PartyType table you would define a "Person" and an "Organization" type. Then each party you create would have a PartyType associated with it. This way you could add more types in the future and your tables are denser, without as many null columns.

Tom Hazel
  • 3,232
  • 2
  • 20
  • 20