mysqlpostgresqldatabase-designerddatabase-diagram

How to model diamond like many-to-many relationship in database ERD

organization_id as foreign key organization_id as primary key

Legend:

How to model a diamond like (if term is correct) relationship? Better to explain using a simplified example:

There is organization, item and tag entities.

My aim is to model:

  1. Every tag is unique by itself and belongs to a single organization.
  2. Every item is unique by itself and belongs to a single organization.
  3. Items have many tags (joined using M2M table) and related tag/item pairs must belong to same organization. (i.e. item from organization A cannot pair with a tag from organization B)

I diagrammed two alternative solutions, but none of them satisfied me.

Diagram 1 breaks 3rd aim: items and tags are unique by themselves using id as primary key, but there is nothing to stop insert pairs into item_tag which belong to different organization.

Diagram 2 does not break, but bends 1st and 2nd aims: organization_id is added as a Primary and Foreign Key to item and tag tables and item_tag.organization_id column references both. This prevents pairs from different organization. tag.id and item.id columns are part of a unnecessary composite primary key now, because in reality single id column represents uniqueness of the item and tag.

How can I model those requirements correctly?

Solution

  • To enforce referential integrity, you'll have to ...

    If you don't include the organization_id (logically redundantly) there would be nothing to keep you from linking items and tags from different organizations (by mistake).

    That would be your diagram 2. But do you really need data type uuid for tags? bigint or even int should suffice, while being a bit smaller and faster.

    Closely related case with code example for PostgreSQL: