Rules on relationships between different SQL tables

c# database-design entity-framework-core sql

Question

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

  • Party
  • Contract
  • Damage

Relations

  • A contract is always linked to a party (pretty easy)
  • A damage must be linked to a party but can optionally be linked to a contract

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.

1
2
3/5/2020 1:39:54 PM

Popular Answer

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.

1
3/5/2020 2:20:47 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow