Re: Best conception of a table

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