I am trying to model several different use cases that require "inheritance" in my Postgres database, and realizing that there are several ways to do this, and I'm not sure if this is the best.
Two specific use cases that I think of are as follows:
"Memberships" - where one "user" can be a member of various types of objects: "teams", "collections" or "elements". In this case, the types of membership are limited to these three types of relationships. (Presently.)
"Events" - where an "event" can be created related to any other object in my data model - for example. "teams.create", "users.update", "webhooks.create", "items.publish" etc. In this case, the “types” of relations are not really limited, because whenever I add a new data model, there is a good chance I want events to be created for it.
Here are a few ways I could implement these needs ...
(Using the Memberships example for these code snippets, but the same applies to the use of Events.)
1. XOR Columns
CREATE TABLE memberships ( id TEXT, user_id TEXT, team_id TEXT, collection_id TEXT, item_id TEXT, PRIMARY KEY (id), FOREIGN KEY (user_id) REFERENCES users (id), FOREIGN KEY (team_id) REFERENCES teams (id), FOREIGN KEY (collection_id) REFERENCES collections (id), FOREIGN KEY (item_id) REFERENCES items (id), CHECK ( (team_id IS NOT NULL)::integer + (collection_id IS NOT NULL)::integer + (item_id IS NOT NULL)::integer = 1 ) );
Pros:
- The database handles external relationships for you.
- The spelling of
JOIN less verbose.
Minuses:
- Each row has additional
NULL column values. - Is the
CHECK constraint a bit weird? - New “types” require the addition of a
*_id column and CHECK changes.
2. A pair of columns
CREATE TABLE memberships ( id TEXT, user_id TEXT, target_type TEXT, target_id TEXT, PRIMARY KEY (id), FOREIGN KEY (user_id) REFERENCES users (id), CHECK (target_type IN ('team', 'collection', 'items')) );
Pros:
- Adding a new type does not require adding a column.
- Each row has no additional
NULL column values. - You do not need a constraint to ensure that there is only one goal for each row.
Minuses:
- You must ensure data consistency at the application level.
- Writing
JOIN requests is a bit more verbose. - When adding new "types" you need to change the
CHECK constraint.
3. Many tables
CREATE TABLE team_memberships ( id TEXT, user_id TEXT, team_id TEXT, PRIMARY KEY (id), FOREIGN KEY (user_id) REFERENCES users (id), FOREIGN KEY (team_id) REFERENCES teams (id) ); CREATE TABLE collection_memberships ( id TEXT, user_id TEXT, collection_id TEXT, PRIMARY KEY (id), FOREIGN KEY (user_id) REFERENCES users (id), FOREIGN KEY (collection_id) REFERENCES collections (id) ); CREATE TABLE item_memberships ( id TEXT, user_id TEXT, item_id TEXT, PRIMARY KEY (id), FOREIGN KEY (user_id) REFERENCES users (id), FOREIGN KEY (item_id) REFERENCES items (id) );
Pros:
- The database handles relationships with foreign keys for you.
- There are no additional
NULL column values in any rows.
Minuses:
- As a result, you create many tables, one for each type of relationship.
Approach A couple of columns seems widespread, but that means the database no longer automatically processes foreign key relationships for you, which seems like a pretty big flaw - things like CASCADE are no longer an option. It seems to me that this is too much negativity to make it a legitimate option.
Approach Many tables are also widespread, and for the case of using "Membership" this makes sense. But for other use cases, such as "Events" (where an event can be created that relates to any object in your database, and not to a pre-limited list), then it seems cumbersome to have so many tables.
The XOR Columns approach is not so widespread, or at least I could not find so much information about it, but it feels like a good balance that still allows the database to do all the work without adding a lot of tables.
Are there any glaring issues with the XOR Columns approach?
Is there a recommended way to solve this problem?