Best conception of a table - Mailing list pgsql-general
| From | hmidi slim |
|---|---|
| Subject | Best conception of a table |
| Date | |
| Msg-id | CAMsqVxvTLNrhiNrgLm0XKv9VU6m22ox8umkzX4OCRgK-5LVCbg@mail.gmail.com Whole thread Raw |
| Responses |
Re: Best conception of a table
Re: Best conception of a table |
| List | pgsql-general |
Hi,
I want to get some different opinions and tips about two different conception. create table product_price_period {
id integer ,
product_id integer,
occupation_type_id integer,
price_mode_id integer,
price_period daterange,
days_checked integer[],
CONSTRAINT product_price_period_id PRIMARY KEY(id) NOT NULL,
CONSTRAINT product_price_occupation_type_id FOREIGN KEY(occupation_type_id)
REFERENCES occupation_type(id)
CONSTRAINT product_price_price_mode_id FOREIGN KEY(price_mode_id)
REFERENCES price_mode(id)
CONSTRAINT product_price_product_id FOREIGN KEY(product_id)
REFERENCES product(id)
}
create table price_mode {
id integer PRIMARY KEY NOT NULL,
name character varying(255)
}
create table occupation_type {
id integer PRIMARY KEY NOT NULL,
name character varying(255)
}
create table product {
id integer PRIMARY KEY NOT NULL,
name character varying(255),
address character varying(255),
status boolean
}
To clarify the purpose of the table 'product_price_period': If I have a product and I choose period like [2018-05-01, 2018-05-31] and in the days_checked = [0,2,3].
The values of days_checked are the indexes of the week's day.In this case 0 => sunday, 2 => tuesday, 3 => wednesday.
So the product is not product for every sunday and tuesday and wednesday in the given period.
The problem with this design is when I make a select to fetch all the product available for a given period, I have to generate all the dates of a given period and then eliminate the dates corresponding to days_checked and after that return the products.
E.g:
If I want to fetch all the products in a period of [2018-05-01, 2018-05-08]
And considering that I have a list of products :
1) product_id_1 [2018-04-01, 2018-05-05] [0,2]
2) product_id_2 [2018-05-01, 2018-05-01] [2]
3) product_id_3 [2018-04-01, 2018-05-17] []
2) product_id_2 [2018-05-01, 2018-05-01] [2]
3) product_id_3 [2018-04-01, 2018-05-17] []
The result wil be product_id_3.
This solution need a huge effort and I tried to solve that with CTE.
Solution2:
In the second solution I keep the same tables but I added another table called 'product_price_period':
create table product_price_sub_period {
id integer PRIMARY KEY NOT NULL,
product_price_period_id integer,
sub_period daterange,
CONSTRAINT product_price_sub_period FOREIGN KEY(product_price_period_id)
REFERENCES product_price_period(id)
}
It's a temporary table and filled using a trigger. The trigger insert or update the table if any row was added or updated in the table product_price_period.
I want to know if it's a good practice to use temporary tables (when should temporary tables will be used) or I use CTE and keep the first solution (despite the long query that I should to write in order to select the data)?
Every solution, tip or advice will be welcome.
Thanks.
pgsql-general by date: