6

organization_id as foreign key organization_id as primary key

Legend:

  • PK (Blue): Primary key
  • FK (Green): Foreign key
  • PFK (Blue): Primary Key and Foreign Key at the same time

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?

özüm
  • 1,166
  • 11
  • 23
  • `FOREIGN KEYs` have limitations. You are possibly hitting their limits, in which case, you need to write application code to enforce the constraints. – Rick James Jun 19 '18 at 03:06

1 Answers1

6

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

  • include organization_id in all tables
  • create logically redundant UNIQUE (or PK) constraints on (organization_id, id) in both tables tag and item
  • have multicolumn FK constraints in item_tag matching the columns of those UNIQUE constraints.

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Brandsletter, thanks for the answer. uuid is used here for example purposes. Proposed solution is exactly same as diagram 2 (`PFK`: Primary + foreign key), but I want to avoid filling unnecessary columns to `UNIQUE` or `PK`. – özüm Jun 19 '18 at 06:59
  • @ozm: It is indeed. I tried the same before and did not find a better way without redundant `UNIQUE` constraint as demonstrated in the linked answer. – Erwin Brandstetter Jun 19 '18 at 11:26