what is the correc way to join this tables?

Party, person and organization:


Party: id, party_type (person/organization)
Person: party_id, name, etc
Organization: party_id, name, etc

Other way (found in https://softonsofa.com/how-to-store-clients-in-database-that-is-party-model-implemented-in-laravel-4-part-1/ ):


Party: id, partyable_id, partyable_type (person/organization) 
Person: id, name, etc
Organization: id, name, etc



In this case, Party is a superclass. Person and Organization are subclasses. The superclass/subclass situation comes up all the time in the real world.
Unfortunately the relational model doesn't contain inheritance, which is the best way to model this.

Your two answers are variations on one design pattern, called Class Table Inheritance.
In this pattern, each subclass has its own table, and there is one additional table for the superclass, making a total of three tables in this case.

Your two answers differ in that your first answer uses an additional pattern, Called Shared Primary key. In the person and Organization tables, the first column is a foreign key that references the Party table. You didn't mention this, but Party_id can also be declared as the primary key in these two tables. It is guaranteed to be unique. It is generally good practice to declare a foreign key, when that is logically correct.

There is another pattern calls Single Table Inheritance. This pattern was suggested by Vesa Krjalainen in a comment. In this pattern, all the data is combined in one in one table, and individual cells are left NULL when they don't pertain to the instance at hand.

For instance, if Person had an attribute, gender, that doesn't exist for organizations, then organizations would just have NULL for gender.

Single Table Inheritance works well in small and simple cases. Class Table Inheritance tends to work well in large complex cases.

If you use Shared Primary Key and declare primary keys for all three tables, then the joins you will need will be simple, easy, and fast. Fast because the necessary indexes will be there, and any good DBMS will use them.

If you want to see several questions that are like this one, click on the tag.

| improve this answer | |
  • Also, depending on your selected database, table inheritance might be supported. Like, in PostgreSQL. And I didn't suggest making all fields of both types in a single table. A modern approach would be to pack variable fields in one single JSON field. Most databases support that. – Vesa Karjalainen Dec 30 '19 at 1:02

Not the answer you're looking for? Browse other questions tagged or ask your own question.