Skip to main content
Database Administrators

Return to Answer

added 55 characters in body
Source Link
Claudio Floreani
Claudio Floreani
  • 92
  • 1
  • 14

(Possible solutions, which I don't like by the way)

USE A COLUMN FOR DATA TYPE

USE A COLUMN FOR DATA TYPE

(Possible solutions, which I don't like by the way)

USE A COLUMN FOR DATA TYPE

Explained problems with the first attempted solution and added another solution which addresses them.
Source Link
Claudio Floreani
Claudio Floreani
  • 92
  • 1
  • 14

I will try to provide a possible solution followingUSE A COLUMN FOR DATA TYPE

Following the suggestion by @a_horse_with_no_name to use a column type to describe the referenced column.

The, a first part wouldattempt could be modelingto model cost, price and shipping using two columns each, one holding a column type and the other holding the actual value. We

We can design the database like this:

The second part is toAnd then 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…)

The problem with this approach is that each type column still needs to know which is the associated value column (and viceversa): if value_1 is renamed "val_1" we would have the same drawbacks as using a column each.

USE A HASH STRUCTURE TO STORE THE VALUES ALONG WITH THEIR TYPES

A better approach could be to store cost, price and shipping in a single column holding a hash structure:

hstore(ARRAY['C','10'], ARRAY['P','40'],ARRAY['S','20'])
hstore(ARRAY['C','55'], ARRAY['P','60'],ARRAY['S','30'])
hstore(ARRAY['C','50'], ARRAY['P','85'],ARRAY['S','10'])

Resulting in

Items
id    amounts
-----------------------------------
1     "C"=>"10","P"=>"40","S"=>"20"
2     "C"=>"55","P"=>"60","S"=>"30"
3     "C"=>"50","P"=>"85","S"=>"10"

In this way we could add data types, name and rename them as wish (either in the application or in another table with TYPE, NAME), and the application could raise an exception to handle the removal of any data type.

The main drawback with this approach is the performance penalty.

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:

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

USE A COLUMN FOR DATA TYPE

Following the suggestion by @a_horse_with_no_name to use a column type to describe the referenced column, a first attempt could be to model 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:

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

The problem with this approach is that each type column still needs to know which is the associated value column (and viceversa): if value_1 is renamed "val_1" we would have the same drawbacks as using a column each.

USE A HASH STRUCTURE TO STORE THE VALUES ALONG WITH THEIR TYPES

A better approach could be to store cost, price and shipping in a single column holding a hash structure:

hstore(ARRAY['C','10'], ARRAY['P','40'],ARRAY['S','20'])
hstore(ARRAY['C','55'], ARRAY['P','60'],ARRAY['S','30'])
hstore(ARRAY['C','50'], ARRAY['P','85'],ARRAY['S','10'])

Resulting in

Items
id    amounts
-----------------------------------
1     "C"=>"10","P"=>"40","S"=>"20"
2     "C"=>"55","P"=>"60","S"=>"30"
3     "C"=>"50","P"=>"85","S"=>"10"

In this way we could add data types, name and rename them as wish (either in the application or in another table with TYPE, NAME), and the application could raise an exception to handle the removal of any data type.

The main drawback with this approach is the performance penalty.

Source Link
Claudio Floreani
Claudio Floreani
  • 92
  • 1
  • 14

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