Question 302197 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to generically model many to many entity relationships in SQL?

I have a SQL database for maintaining D&D games which has tables for things like Characters, Items, and Locations. I want to start modelling the relationships between all these different entity types, such as the relationship a Character has with another Character, or the relationship a Character has with a particular Item or Location, essentially creating a graph of how everything in a game is linked together.

What I tried was initially starting from the perspective of Character relationships, creating a many to many join table that looks like the following:

CREATE TABLE character_relationships (
    character_id_from UUID REFERENCES characters(id),
    character_id_from_id_to UUID REFERENCES characters(id),
    relationship_type TEXT,
    opinion TEXT,
    -- Additional relationship-specific attributes
);

but if I want to be able to generically model relationships from any entity type such as characters to items, or items to locations, what’s the best way of handling this?

I’m thinking I may need to create a "superclass" table called Entity, which is inserted into first to retrieve the ID, and then used to insert into the subclass Characters, Items, and Locations tables, with a resulting join table like this:

CREATE TABLE entity_relationships (
    entity_id_from UUID REFERENCES entities(id),
    entity_id_from_id_to UUID REFERENCES entities(id),
    relationship_type TEXT,
    opinion TEXT,
    -- Additional relationship-specific attributes
);

Is this the best way to design generic relationships or is there an alternative besides creating multiple tables for each relationship type? What would be the potential downsides of doing it this way?

Tags:

2

Answers


  1. 0

    but if I want to be able to generically model relationships
    from any entity type such as characters to items, or items
    to locations, what’s the best way of handling this?

    Don’t go higher-level if you don’t need to, stay concrete: just create one "join table" for every such relatioship. The semantics stays direct and clear (you can see what entities there actually are), your queries also stay simple and to the point, as well as defining constraints remains straightforward.

    I’m thinking I may need to create a "superclass" table called Entity,
    […] What would be the potential downsides of doing it this way?

    The downside is that this is the opposite as above, it’s extra-complexity at all levels, indeed most of the logic that is usually in charge of the DBMS would have to be coded manually, up to using triggers to ensure the consistency of the relationships (to ensure the relatioship_type is compatible with the referred entities, and similar). So, this is simply not the way to go unless the domain does have an open collection of entity types.

    Login or Signup to reply.
  2. 0

    Alternative, with its own pros and cons… ‘best’ is a hard word to quantify… seen and done this issue lots of ways for lots of reasons.

    A single join table that uses composite keys for the related entities…
    thing1, thing2, relationship_type, opinion… (not quite as abstract as the superclass Entity)

    where thing1 and thing2 are composite keys like ‘person:{guid}’, or ‘item:{guid}’, or ‘location:{guid}’.

    Cons: As mentioned above in JDE’s answer, your code takes on the work for maintaining the join table.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search