2

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.

Jesse
  • 544
  • 5
  • 24
  • One way of enforcing your rule at the database level is to use a composite primary key for the `Contract` table. However, you mention you want to use `guid`s. – The Impaler Mar 05 '20 at 13:49
  • Would it work to have a fourth table act as an intermediary. It would have a foreign key to PartyID, but also to DamageID (not null) and ContractID (nullable). – JJ32 Mar 05 '20 at 15:01
  • @JJ32 that would only move the problem to a different table – Jesse Mar 05 '20 at 15:17

2 Answers2

1

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.

The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • It's probably worth explaining how NULLs are handled in composite foreign keys, and possibly even the logic involved in having a natural key field (for the GUIDs) so that the surrogate keys can be generated. – MatBailie Mar 05 '20 at 14:17
  • @MatBailie Was thinking about that, but that depends on the database engine. 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. – The Impaler Mar 05 '20 at 14:19
  • Then that needs to be in your answer too, as there are environments in which you solution isn't robust; the Op would need to know that ;) *(I didn't, I've only encountered none and full)* – MatBailie Mar 05 '20 at 14:20
  • Trying to figure out on how to do this in EF Core though, any thoughts? – Jesse Mar 05 '20 at 14:50
  • @Jesse I don't know EF Core much, but this is just plain, old, standard SQL database. I would assume it's covered. – The Impaler Mar 05 '20 at 15:23
0

You can enforce this behaviour with constraits on the table, so data integrity is enforced by the database and invalid values can not be inserted. For example, with SQL Server we can create a similar solution to this answer.

CREATE FUNCTION dbo.IdMismatches()
RETURNS int
AS BEGIN RETURN (
    SELECT COUNT(*)
    FROM Damage d
    JOIN Contract c ON d.ContractId = c.Id
    WHERE d.PartyId != c.PartyId
) END
go
ALTER TABLE Damage ADD CONSTRAINT chkMismatches CHECK (dbo.IdMismatches() = 0);

I don't know if this is the kind of 'database design' you were looking for, but I think there is no way to avoid the need to use these kind of checks. This way at least the SQL Server enforces the constrait.

I also suggest that you still check this constrait on the server with the method you mentioned - there is no problem fith validating user input on server side or checking business intelligence constraits.

Annosz
  • 992
  • 6
  • 23