0

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

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
); 
8
  • Thanks! I should probably clearify that I don't believe the pesudocode at the bottom is valid syntax (in postgresql), it is just to give an idea of what i was looking for. Maybe it can be done with this extra_type idea?
    – filipot
    Apr 3 at 16:22
  • 1
    Can you elaborate on how that should be done with a single UUID representing either a car or a person? With or without a type classifier, I don't see how it is possible to represent a union type and still use referential integrity
    – Lennart
    Apr 3 at 16:56
  • @Lennart My understanding was OP's new table proposal would replace the individual tables, and so the only referential integrity you would need is between the extra table. Otherwise yes to enforce the referential integrity with a foreign key constraint, you would need the type field I suggested.
    – J.D.
    Apr 3 at 21:22
  • @filipot Yes, I misunderstood your use case a little but Lennart clarified that for me. You would need the type column I suggested to be able to officially enforce referential integrity with a foreign key constraint.
    – J.D.
    Apr 3 at 21:25
  • 1
    @J.D. Your extended example explained your thoughts. I did not read that into the op:s question, but I often misread things:-)
    – Lennart
    Apr 4 at 8:02
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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