What's the best way to design multiple 1 to 1 relation within Postgre

Question

I would like to know if there's a better way to design this kind of relationships within Postgre:

Basically, I've got some entities that are either an EntityA, an EntityB or an EntityC. I will have plenty of other relationships between entities and other stuff (like Comment in this case) so I've got this generic Entity that defines which type of Entity it's linked to.

As an example, if we have an EntityA linked to a comment, the Entity entry will look like: type: EntityA entity_a_id: x entity_b_id: null entity_c_id: null

Is there a better way to design this?

This is going to cause you much pain later - you will end up writing statements that join from Entity to EntityA B and C every time, since it must cover any scenario. How different are the entities that they should be on different tables and not hosted on a single table? — Andrew, Feb 15 '18 at 15:47
?So no, a record in Entity table can't have a, b and c at the same time. Although [this link](https://weblogs.asp.net/manavi/inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-1-table-per-hierarchy-tph) is angled at SqlServer and C# code, options for modelling the inheritance of multiple subclasses are typically Table per Type (kind of what you've done) and Table per Hierarchy (big fat table with everything). But you wouldn't have the type discriminator AND the entity subclass ids in the base table - you can share the same entity id primary key on the subclass tables. — StuartLC, Feb 15 '18 at 15:49
In that case, this is a terrible way to model it. Provide some sample data that would be in the tables. — dfundako, Feb 15 '18 at 15:49
Each entity is going to be very different, like totally different. Some are going to be a person, another one will be a location, another one will be a scenario. The only thing they have in common will be a name and a photo. The thing is that I need to reference them as if they were the same entity cause all the features on the project applies to every entities (eg: you can comment anyone of them) — Comawoot, Feb 15 '18 at 16:00
Why not just have table EntityAttrib with ID and TypeID being the Key and you join from entity to EntityAttrib on Type and ID. it's one join 100% of the time using a combined key. — xQbert, Feb 15 '18 at 16:03
seems a bit off, take a look at this https://stackoverflow.com/questions/4050784/defining-multiple-foreign-keys-in-one-table-to-many-tables/4051523#4051523 — Damir Sudarevic, Feb 16 '18 at 00:35

score 1 · Answer 1 · answered Feb 15 '18 at 15:58

1

This seems a good fit for the Table Inheritance feature

CREATE TABLE entity (
  -- columns that are shared among all entities
);

CREATE TABLE entity_a (
  -- columns that are unique for this entity type
) INHERITS (entity);

CREATE TABLE entity_b (
  -- columns that are unique for this entity type
) INHERITS (entity);

This way, entity types do not include columns of other types

answered Feb 15 '18 at 15:58

Sharon Ben Asher

13,849
5
33
47

I won't be able to list all available entities at once and identify them by ID. One of the use case will be to mention one of the entities like with @ on Facebook, so I need a list with unique IDs of every entities at once – Comawoot Feb 15 '18 at 16:28

score 0 · Answer 2 · answered Feb 15 '18 at 16:04

0

How different are the entities?

Why not just

CREATE TABLE Entity
(
    EntityId int,
    EntityType enum(A, B, C),
    FieldValue varchar(255)
)

or if the field values are significantly different and don't make sense in a single column,

CREATE TABLE Entity
(
    EntityId int,
    EntityType enum(A, B, C),
    FieldValueX varchar(255),
    FieldValueY varchar(255),
    FieldValueZ varchar(255)
)

answered Feb 15 '18 at 16:04

Kirk Broadhurst

27,836
16
104
169

I don't want to end up with a huge table containing multiple useless fields depending which entity is represented. Actually there will be 5 completely different entities with at least 10 unique fields. – Comawoot Feb 15 '18 at 16:22

What's the best way to design multiple 1 to 1 relation within Postgre

2 Answers2