Ensuring data consistency accross multiple tables in a postgresql Database

Question

The problem is the following:

I have 3 tables: Companies, Bundles, Documents, with foreign key relationships described in the folling diagrams.

I don’t want two documents belonging to the same company to have the same reference. I envision two solutions:

Use a matrialized view with the company_id column of bundles and the reference column of document to create a unique index.
Replicate the company_id column in the documents table, the downside being that i open a way for data inconsistency

Is there any cannonical solution for this problem ?

Is the relationship between Document and Bundle fixed? That is, once a Document is assigned to a Bundle it can never be re-assigned to a different Bundle? Is it ever possible to have a Document which doesn't belong to a Bundle (I guess not but worth asking)? — APC, Apr 01 '22 at 12:31

score 0 · Answer 1 · answered Mar 31 '22 at 14:27

The easiest solution is probably to add a (redundant) unique constraint to bundles (company_id, id), add company_id to documents as well and define the foreign key between documents and bundles on those two columns.

Then a simple unique constraint on documents(reference, company_id) will do the trick.

score 0 · Answer 2 · answered Mar 31 '22 at 16:29

-- Company COM exists.
--
company {COM}
     PK {COM}

-- Bundle number BU# of company COM exists .
--
bundle {COM, BU#}
    PK {COM, BU#}

FK {COM} REFERENCES company {COM}

-- For each company, bundle numbers are {1,2,3..}

-- Document number DO# of company COM is part of 
-- bundle number BU#, of that company.
--
document {COM, DO#, BU#}
      PK {COM, DO#}

FK {COM, BU#} REFERENCES bundle {COM, BU#} 

-- For each company, document numbers are {1,2,3..}

Note:

All attributes (columns) NOT NULL

PK = Primary Key
FK = Foreign Key

Using suffix # to save on screen space.
OK for SQL Server and Oracle, for others use _NO.
For example, rename DO# to DO_NO.

If you need, or prefer, single-column PKs, you can ADD them as described in this example.

Ensuring data consistency accross multiple tables in a postgresql Database

2 Answers2