Re: Best conception of a table - Mailing list pgsql-general

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@aklaver.com