Best conception of a table

[hmidi slim <hmidi.slim2@xxxxxxxxx>]

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.