Correct (as in normalized) way to express a relationship between 3 tables with complex(?) constraints?

Question

I'll be using Postgres for the examples, but feel free to show examples in other databases if needed.

The simplified schema:

create table factory (
  id serial primary key,
  detail text not null
);

create table process (
  id serial primary key,
  detail text not null
);

create table item (
  id serial primary key,
  detail text not null
);

I'll refer Factory as [F], Process as [P] and Item as [I].

The schema has the following conceptual relationships:

• Each Process is exclusive to one Factory, so [P] n -> 1 [F]
• Each Item is exclusive to one Factory, so [I] n -> 1 [F]
• Each Item can be made by multiple Processes, so [P] n -> 1 [I]

To express this relationships I came up with:

create table factory_item_process (
  factory_id integer not null references factory(id),
  process_id integer not null references process(id),
  item_id integer not null references item(id),
  constraint pk_factory_item_process primary key (
    factory_id, process_id, item_id
  ),
  constraint uq_factory_item_process_process unique (process_id)
);

This takes care of [P] n -> 1 [I] and [P] n -> 1 [F], but doesn't solve [I] n -> 1 [F].

There is no way to convey [I] n -> 1 [F] with unique key constraints, so I created a simple function that checks if [I] already belongs to an [F]:

create function in_other_factories(
  factory_id integer, 
  item_id integer
) returns boolean
language sql returns null on null input
  return true in (
    select 
      true 
    from factory_item_process
    where factory_id <> $1
      and item_id = $2
  );
alter table factory_item_process 
  add constraint chk_factory_item check(not in_other_factories(factory_id, item_id));

What is right & wrong about this to express these relationships?

Making a check with a user-defined function is a code smell.

I don't if the tables are correctly normalized. I chose to use another table for this relationship because this relationship will have attributes that are exclusive to itself.

Answer 1

Looking at your relationships:

• Each Process is exclusive to one Factory, so [P] n -> 1 [F]
  This would mean that process should have a factory_id column

• Each Item is exclusive to one Factory, so [I] n -> 1 [F]
  This means item should have a factory_id column

• Each Item can be made by multiple Processes, so [P] n -> 1 [I]
  It would be logical here to have a item_created column on process. It's not clear if a Process can exist without having created an Item, if so then this column should be nullable.

There is no need for a fourth table, as the relationship between a Process on the one side, and an Item and Factory on the other, is not many-to-many, it is many-to-one.

The only thing remaining is to enforce that a Process can only create an Item from the same Factory. We can do this by using a multi-column foreign key. Note that this means that item needs a seemingly extraneous unique constraint for this to apply to, but it may be useful to have this for indexing purposes anyway.

create table factory (
  id serial primary key,
  detail text not null
);

create table item (
  id serial primary key,
  detail text not null,
  factory_id int not null references factory (id),
  unique (factory_id, id)
);

create table process (
  id serial primary key,
  detail text not null,
  factory_id int not null references factory (id),
  item_created int not null,
  foreign key (factory_id, item_created) references item (factory_id, id)
);

db<>fiddle

Answer 2

Each Item is exclusive to one Factory

This can be understood as the Factory being an attribute of an Item.

Similarly,

Each Process is exclusive to one Factory

makes the Factory an attribute of a Process. With that, the only other relationship you need is that between Items and Processes, which is arguably a many-to-many one, as one can imagine that, while an Item can be made by multiple processes, a Process can be involved in making many Items.

If, as you indicate in a comment, a Process can only participate in creation of a single Item, the Process itself, via its Factory attribute, already enforces the fact that the Item "belongs" to the same Factory, so no extra constraint or attribute of Item is needed. A