Single foreign key for referencing one of multiple tables

Question

Currently I have a schema that looks like this:

create table person(
    id uuid primary key default gen_random_uuid() not null,
);
create table car(
    id uuid primary key default gen_random_uuid() not null,
);
create table extra(
    id uuid primary key default gen_random_uuid() not null,
);
create table car_extra_m2m(
    car_id uuid not null references car(id) on delete cascade,
    extra_id uuid not null references extra(id) on delete cascade,
    primary key (car_id, extra_id)
);
create table person_extra_m2m(
    person_id uuid not null references person(id) on delete cascade,
    extra_id uuid not null references extra(id) on delete cascade,
    primary key (person_id, extra_id)
);

Is it possible to express the two many to many tables in a single many to many table? Since we're using uuid the ids should never collide, so it might be possible to know the type just from the uuid?

Like this pseudocode: (Edit: not valid syntax in postgresql, does there exist a valid syntax for this?)

create table extra_m2m(
    person_or_car_id uuid not null references (person(id) or car(id)) on delete cascade,
    extra_id uuid not null references extra(id) on delete cascade,
    primary key (person_or_car_id, extra_id)
);

J.D. · Accepted Answer · 2021-04-04T00:51:00.007

Yea there's no reason not to if it fits your use case better, there's nothing wrong with that design. My only suggestion would to add a extra_type field to explicitly identify whether the record is of type person or car. You'll likely find a field like that will be helpful later on. But yes that's a valid schema design.

After further clarification in the comments, you'd have to make a couple changes to natively accomplish this to still be able to support foreign key constraints in the database (otherwise what you'd be looking for is a polymorphic foreign key which isn't natively supported).

The first change is you'd have to merge your person and car table into a single table, e.g. person_or_car with the person_or_car_id primary key field. The second change is you'd need to add an extra_type_id field to the person_or_car table to distinguish between the two. And finally you'll need to add the extra_type_id field to the extra_m2m table, and create the foreign key constraint on it.

Example query with the extra_type_id field, as discussed in the comments, for step 3:

create table extra_m2m
(
    person_or_car_id uuid not null,
    extra_type_id int,
    extra_id uuid not null references extra(id) on delete cascade,
    primary key (person_or_car_id, extra_id),
    foreign key (person_or_car_id, extra_type_id) references person_or_car (person_or_car_id, extra_type_id) on delete cascade
);

David Spillett · Answer 2 · 2021-04-03T21:58:29.590

I think both are valid and it comes down to other requirements, particularly reporting.

If you ever need to report on extras irrespective to what they related to (or by what they relate to) then having all the relationships in one table would be more efficient than needing to union two selects.

But if you end up wanting extra fields detailing each relationship between extras and other entities and those details varied between cars/people/other, you would end up starting to want to split things out again using a table inheritance pattern and so end up with three tables in place of your two.

Without knowing the likelihood of such requirements now or in the future it isn't clear which way is most right for your needs, but I'd say neither is categorically wrong.

score 1 · Answer 3 · edited Aug 31 '23 at 06:43

One solution is to use record keys independent of data record. We use a GUID for each record. Foreign key constraint to one of several tables is accomplished by keeping a table of "keys" to which foreign key constraint is made.

In case at point person, car and extra would each contain a unique key that must exist in the key table, possibly accompanied by a code to indicate the owning table. The foreign keys in the table defining the relation between items in those tables would reference the key table. Records in the table defining the relation would each have their own key in the key table, as well.

We have a parameterized stored procedure to insert records that obtains a new key value if one is not present in the submitted record. The new key is inserted in the key table, then the new record is inserted in the respective table. An existing key would cause an exception on insert (should be an update).

Single foreign key for referencing one of multiple tables

3 Answers3