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