On 05/11/2018 03:54 PM, hmidi slim wrote:
Hi,
I want to get some different opinions and tips about two different
conception.
I want to create a table named proudct_price_period
createtableproduct_price_period {
id integer,
product_id integer,
occupation_type_id integer,
price_mode_id integer,
price_perioddaterange,
days_checked integer[],
CONSTRAINTproduct_price_period_id PRIMARYKEY(id) NOTNULL,
CONSTRAINTproduct_price_occupation_type_id FOREIGN KEY(occupation_type_id)
REFERENCESoccupation_type(id)
CONSTRAINTproduct_price_price_mode_id FOREIGN KEY(price_mode_id)
REFERENCESprice_mode(id)
CONSTRAINTproduct_price_product_id FOREIGN KEY(product_id)
REFERENCESproduct(id)
}
This table has relations with other tables such as 'product',
'price_mode' and 'occupation_type' which have these schema:
createtableprice_mode {
id integerPRIMARYKEYNOTNULL,
namecharactervarying(255)
}
createtableoccupation_type {
id integerPRIMARYKEYNOTNULL,
namecharactervarying(255)
}
createtableproduct {
id integerPRIMARYKEYNOTNULL,
namecharactervarying(255),
addresscharactervarying(255),
statusboolean
}
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.
The result does not seem to correspond with your problem description.
The description would seem to indicate you are looking for any product
available at any time during the period. That would include
product_id_1. The result you show says you are only looking for a
product that is available for all days during the period. Can you
clarify this?
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':
createtableproduct_price_sub_period {
id integerPRIMARYKEYNOTNULL,
product_price_period_id integer,
sub_period daterange,
CONSTRAINTproduct_price_sub_period FOREIGN KEY(product_price_period_id)
REFERENCESproduct_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.
**
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
