Ensuring data consistency accross multiple tables in a postgresql Database-CodePudding

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 ?

CodePudding user response：

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.

CodePudding user response：

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