Re: Table schema inhancement

On 04/12/2018 05:59 PM, hmidi slim wrote:
> 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 <http://occupation_type.name>
> */
> /*from price_per_occupation inner join product_price_period on
> product_price_period.id <http://product_price_period.id>=
> price_per_occupation.product_price_period_id
> */
> /*inner join occupation_type on occupation_type.id
> <http://occupation_type.id> = price_per_occupation.occupation_type
> */
> /*inner join product on product.id <http://product.id> =
> product_price_period.product_id
> */
> /*where product_price_period.price_period @> '[2018-07-22, 2018-07-23]'
> */
> /*and occupation_type.id <http://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?

My 2 cents eliminate the is_* fields and create a single field:

dow_verified integer[]

Then assuming non-iso week day number Sunday(0) --> Saturday(6) in the
array enter the day of week(dow) numbers for verified days e.g.:

ARRAY[0, 1, 4]

for Sunday, Monday, Thursday.

Then to get the days that are not verified over a period something like:

WITH dp AS (
SELECT
extract('dow' FROM generate_series('04/01/18'::date,
'04/14/18'::date, '1 day'))
AS dow,
generate_series('04/01/18'::date, '04/14/18'::date, '1 day')
AS dt
)
SELECT
dp.*
FROM
dp
WHERE
dp.dow NOT IN (
(
SELECT
*
FROM
unnest(ARRAY [ 0, 1, 4 ]) AS dow_dt)
)

ORDER BY
dt;

dow | dt
-----+------------------------
2 | 2018-04-03 00:00:00-07
3 | 2018-04-04 00:00:00-07
5 | 2018-04-06 00:00:00-07
6 | 2018-04-07 00:00:00-07
2 | 2018-04-10 00:00:00-07
3 | 2018-04-11 00:00:00-07
5 | 2018-04-13 00:00:00-07
6 | 2018-04-14 00:00:00-07
(8 rows)

> |
> ||
> ||
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com