Single foreign key for referencing one of multiple tables

All we need is an easy explanation of the problem, so here it is.

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)
);

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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
); 

Method 2

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply