Hi,
I want to get some different opinions and tips about two different
conception.
I want to create a table named proudct_price_period
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)
}
This table has relations with other tables such as 'product', 'price_mode'
and 'occupation_type' which have these schema:
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] []
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.
