Thread: Best conception of a table
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.
hmidi slim wrote: > 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 should be possible without using a temporary table. First filter out the products whose daterange does not contain your interval, then remove all products where generate_series(DATE '2018-05-01', DATE '2018-05-08') contains one of the forbidden week days. Maybe you should choose a simpler data model, like storing all allowed days for a product in an array (you can use a GIN index to speed up the <@ operator). Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
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