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…)