#StackBounty: #postgresql #database-design #foreign-key #relational-theory #referential-integrity How to store a (record which holds a)…

Bounty: 100

TL;DR: If the database schema should hold all the business logic, how is it be possible to specify that an attribute type is a reference to a specific attribute, instead of a specific record (as is the case of a foreign key)?

To make an example, let’s suppose I have a table "Discounts" with a column "share" which holds the percentage to be applied to the value of column "cost", "price" or "shipping" of the table "Items".

"Discounts" also holds a foreign key to "item_id".

I need to add another column "base" to table "Discounts" where to store a reference to one of the column of table "Items", and calculate the percentage of the value of that column.
For example, given these values:

share    base                 item_id
50       (item's cost)        3
25       (item's price)       1
100      (item's shipping)    2

id    cost    price    shipping
1     10      40       20
2     55      60       30
3     50      85       10

I want to be able to calculate:

  • 50% of 50 (cost of item 3)
  • 25% of 40 (price of item 1)
  • 100% of 30 (shipping of item 2)

The column "base" should contain neither the number (e.g. 3) nor the name (e.g. "price") of the referenced column, because the name or the order of each table could change.
In particular a database doesn’t have any knowledge about the columns (attributes) order or the rows (records/tuplets) order, infact the RDB theory asserts that «the tuples of a relation have no specific order and that the tuples, in turn, impose no order on the attributes.»

Instead if we rely on the column names, we should enforce that each entry holds a valid attribute name, and whenever the attribute name changes, then we must change its records, constraints and app’s validations. If the name is referred in multiple relations, maintaining the database integrity becomes very complex.

The problem here is that we are not writing a reference to the attribute name in the database schema (like when we add a foreign key), but into the data themselves, and this seems a very bad practice, since it threatens the referential integrity.

If there is no DB agnostic way to do this, then assume the database is PostgreSQL (v12+).

Get this bounty!!!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.