I am working on a database design case which is very flexible and not sure how to handle it properly or what kind of pattern this is.
This is a simplified version to illustrate the problem I have:
Entities
Relations
So, the last relation is the problem here. Since our endpoint is an API and people could potentially send the guid of Party A and the guid of Contract Y which is linked to Party B.
Which then the damage is owned by either Party A or B and this shouldn't be possible. Could add a check when adding the damage to make sure Party == Contract.Party but it feels like I am fooling the relationship database.
I am unsure on how this problem is called and how to solve it.
You can enforce this relationship automatically at the database level using a composite key. However, you won't be able to use guids. If using guids is a requirement on your problem, then this solution won't be useful to you.
The solution shown below will work on any relational database, without extra code:
create table party (
id int primary key not null
);
create table contract (
party_id int not null,
contract_no int not null,
primary key (party_id, contract_no),
constraint fk1 foreign key (party_id) references party (id)
);
create table damage (
id primary key not null,
party_id int not null, -- not nullable
contract_no int, -- nullable
constraint fk2 foreign key (party_id) references party (id),
constraint fk3 foreign key (party_id, contract_no)
references contract (party_id, contract_no)
);
Note: The SQL standard defines three matching options for partially null FKs (none, partial, full) but each database is a liberty to use any of those.