1

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.

4
  • Please: Ask 1 specific researched non-duplicate question. Avoid social & meta commentary in posts. don't insert "EDIT"s/"UPDATE"s, just make your post the best presentation as of edit time. Yes or no questions are seldom helpful or asking for what is actually wanted. How to Ask Help center Database Administrators Meta Meta Stack Exchange
    – philipxy
    Nov 14, 2023 at 8:03
  • Those are not relationships, they are cardinalities of relationships. A relationship/association is represented in a relational DBMS & in the ERM by a table/relation. "Relationship" is also frequently misused to mean "foreign key constraint.
    – philipxy
    Nov 14, 2023 at 8:05
  • "Each Item can be made by multiple Processes" for a relationship on (p,i) (in that order) doesn't say whether a process can make multiple items, so it implies a cardinality that's not 1:1 or 1:M but it could be M:1 or M:M. So your "so [P] n -> 1 [I]" is wrong. Similarly the preceding "each" statements don't exclude N being 1. But anyway those 3 cardinalities don't make sense for a 3-entity relationship table & they aren't necessarily the cardinalities of its 2-column projections. You need to state what each relationship is, ie when a row is in it. PS Please clarify via edits, not comments.
    – philipxy
    Nov 15, 2023 at 15:32
  • You are confusing notions--association (aka relationship in the sense of association, aka relation in the sense of association), cardinality (of an association), cardinality of a participation (in an association), FK (foreign key) constraint & FD (functional dependency). (Relation is also used in the sense of a table of rows of participants in a relation in the sense of association.)
    – philipxy
    Nov 15, 2023 at 15:50

2 Answers 2

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

1
  • I was not aware that FKs could have multiple references, this pretty much solves it entirely. Thank you very much
    – Cidos
    Nov 10, 2023 at 12:06
0

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

1
  • Thank you very much for your input mustaccio. My specific problem is that I need to enforce that a Process cannot be used for more than one Item, hence one to many. I know I could have factory referenced in both tables, but how do I enforce that the Process and the Item belongs to the same Factory? I chose to use a "fourth" table because the relationship (factory_item_process) will have attributes that are exclusive to itself (in hindsight I should've specified it in my question)
    – Cidos
    Nov 9, 2023 at 17:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.