1

I am a rather inexperienced SQL developer but I am trying to do my best to have a consistent database. The problem I seek to resolve could be done in an upper layer of my system but I am aware that it's best to handle things like that where they belong: In the database layer.

So what I am having is a table where I store offers that may or may not repeat on a defined day:

day_of_week
---------------------------------------------
id  day_name
1   SUNDAY
2   MONDAY
3   TUESDAY
4   WEDNESDAY
5   THURSDAY
6   FRIDAY
7   SATURDAY

offer
---------------------------------------------
id  day_id   valid_from_day  valid_until_day
1   3        2016-03-01      2016-03-01
2   6        2016-03-01      9999-03-01

and here is my problem:

offer
---------------------------------------------
id  day_id   valid_from_day  valid_until_day
1   2        2016-03-01      2016-03-01
2   6        2016-03-01      9999-03-01

day_id 2 makes no sense for 2016-03-01 to 2016-03-01 because the 1st or March, 2016 was a TUESDAY = 3, not a MONDAY = 2.

I want to be able to store arbitrary intervals into this table but I have to make sure that they make sense. What I am looking for is a way to check whether a given interval valid_from_day to valid_until_day contains a given day day_id. This should actually be an easy check but I am not sure if I can use a CONSTRAINT for this or whether I'd have to use a TRIGGER for this and how that would look like.

Stefan Falk
  • 23,898
  • 50
  • 191
  • 378

1 Answers1

0

I think a trigger is best in this case. Write it so that an UPDATE or INSERT is done only if day_id is between extract(dow from valid_from_day::date) and extract(dow from valid_until_day::date). This should fit naturally within how trigger functions work.

There are other variations you can use, check the EXTRACT function.

avip
  • 1,445
  • 13
  • 14