Skip to main content
1 of 3

I will try to provide a possible solution following the suggestion by @a_horse_with_no_name to use a column type to describe the referenced column.

The first part would be modeling cost, price and shipping using two columns each, one holding a column type and the other holding the actual value. We can design the database like this:

Discounts
share    base_type    item_id
------------------------------------------
50       C            3
25       P            1
100      S            2


Items
id    type_1    value_1    type_2    value_2    type_3    value_3
-----------------------------------------------------------------
1     C         10         P         40         S         20
2     C         55         P         60         S         30
3     C         50         P         85         S         10

The second part is to enforce the values of type_1, type_2, type_3 with a constraint, either in the application logic or in the business logic (ADD CONSTRAINT type_1 CHECK (type_1 IN ('C', 'P', 'S') ecc…)