Search Postgresql Archives

Best conception of a table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux