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:
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.