Rules on relationships between different SQL tables

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.

user_6436191 · 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.

user_9513080 · Answer

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.

Rules on relationships between different SQL tables

2 Answers