CODEWITHSUNDEEP
postgresqldatabase-design
frank
frank

Reputation: 1303

Weekly hour allocation problem in Rails and Postgresql

If I have a list of tasks with a certain date ranges, and the task is broken into weekly hour chunks of work (ie. 30 hours from 2018-12-31 to 2019-01-06 ... etc starting from Monday).

The kind of operations I would like to do are

  1. Display all the weekly hours of all the tasks for a list of users
  2. Sum the weekly hours for a user for all his tasks for the week
  3. When the duration of the task is modified, create/destroy the weekly hour chunks.

Would it be more efficient to store these weekly records as

  1. start date/end date/hours,
  2. year/week number/hours

Storing start/end date probably give more flexibility to the table as it could potentially store non-weekly align hours.

Storing week number means given a date range, creating the weekly chunks is as simple as finding the week number of the start date and the week number of the end date, and populating the weeks in between (without converting to date ranges). Also easier validation for updating the hours for a week, as long as the week number is 1-53.

Wondering if anyone has tried out either option and can give any pointers on their preferred option.

Upvotes: 0

Views: 56

Answers (1)

user330315
user330315

Reputation:

I would probably go for a daterange column.

That gives you the flexibility to have differently sized chunks and allows you to define an exclusion constraint to prevent overlapping ranges.

Finding the row for a given week is still quite simple using the "contains" operator @>, e.g. where the_column @> to_date('2019-24', 'iyyy-iw') finds the row(s) that contain week number 24 in 2019.

The expression to_date('2019-24', 'iyyy-iw') returns the first day (Monday) of the specified week.

Finding all rows that are between two weeks can also be done, however construction the corresponding date range looks a bit ugly. You can either construction an inclusive range with the first and last day: daterange(to_date('2019-24', 'iyyy-iw'), to_date('2019-24', 'iyyy-iw') + 6, '[]')

Or you can create a range with an exclusive upper range with the next week's first day: daterange(to_date('2019-24', 'iyyy-iw'), to_date('2019-25', 'iyyy-iw'), '[)')

While ranges can be indexed quite efficiently and , the required GIST indexes are a bit more expensive to maintain than a B-Tree index on two integer columns.

Another downside of using ranges (if you don't really need the flexibility) is that they take up more space than two integer columns (14 byte instead of 8, or even 4 with two smallint). So if the size of the table is of any concern, then your current solution with the year/week columns is more efficient.

"Storing week number means given a date range, creating the weekly chunks is as simple as finding the week number of the start date and the week number of the end date"

If your input is a start and end date to begin with (rather than a "week number"), then I would definitely go for a daterange column. If that start and end date cover more than one week, then you store only one row, rather than multiple rows.

Upvotes: 1

Related Questions