Table schema inhancement

Поиск
Список
Период
Сортировка
От hmidi slim
Тема Table schema inhancement
Дата
Msg-id CAMsqVxsoEQOa95FpL56vja0P+qoAxvuPqZk2ysALz5JG046B4w@mail.gmail.com
обсуждение исходный текст
Ответы Re: Table schema inhancement
Список pgsql-general
Дерево обсуждения
Hi,
I have these tables:
price_per_occupation: id (integer), product_price_period_id(integer), occupation_type(integer), price (numeric)

product_price_period; id(integer), product_id(integer), is_monday(boolean), is_tuesday(boolean), is_wednesday(boolean), is_thursday(boolean), is_friday(boolean), is_saturday(boolean), is_sunday(boolean), price_period(daterange)

occupation_type: id(integer), name(varchar)

product: id(integer), name(varchar)

I run this query:
select price_per_occupation.price, product_price_period.price_period, occupation_type.name
from price_per_occupation inner join product_price_period on product_price_period.id= price_per_occupation.product_price_period_id
inner join occupation_type on occupation_type.id = price_per_occupation.occupation_type
inner join product on product.id = product_price_period.product_id
where product_price_period.price_period @> '[2018-07-22, 2018-07-23]'
and occupation_type.id = 1


This query returns all the products with an occupation_type = 1 and have the period_price between 2018-07-22 and 2018-07-23.

However I need to verify if the boolean values verified.
E.g if is_monday = true the date corresponding to Monday will be eliminated from the period.
if is_sunday = true and is_friday = true the dates corresponding to Sunday and Friday will be eliminated from the period interval.
If I will choose all the products in the interval [2018-04-07,2018-04-14] and is_monday = true and is_thursday= true
the date of monday is 09/04/2018 and date of friday is 13/04/2018.
I have to get all products contained in [2018-04-07,2018-04-08] U [2018-04-10, 2018-04-12] U [2018-04-14, 2018-04-14]

In order to get the date of truthy columns I should execute a function which contains a query like that:

select *
from generate_series(date '2018-04-07', date '2018-04-14', interval '1 day') the day
where extract ('dow', the_day) = 1


I'm face many problems with this schema;
I should verify the boolean values.
I should extract the corresponding dates based to the values.
I decompose the period into different periods then run the query to fetch the products.

How can I enhance the query? or is there any proposition for the schema table to enhance it?



В списке pgsql-general по дате отправления: