DB schedule modeling

[Jskobos]

What is the best way to represent freelancer availability in Postgres? We want to be able to search by availability, so some kind of additional table with foreign key is called for.

Acceptance:

• [ ] Discuss and determine appropriate data model.

• [ ] Update DB Schema page on the wiki with updated model.

[ortonomy]

These could be stored as columns in freelancer table, or as a referenced table with foreign key... but, I think that there will probably be so many unique availability profiles, that having in a separate table just means you'll have separate records which could easily be stored as freelancer data (it is uniquely part of their profile)

Times, days, dates seem like continuous data and hard to store / model.

IDEA: time periods: [weekend morn, weekend after, weekend evenings, weekday evenings, weekday afternoons, weekday mornings] + days off: [mon - sun] + exceptions: [mon-morn,mon-after, ...] + total hours: [0..40]

Assumptions:

• work week monday-friday
• working hours 36 per week 9-5 or 40 9-6
• weekends are sun-mon
• that people group weekend days together
• that people take whole days off

Strengths:

• easily filterable as different discrete dimensions of availability data
• allows unique availability profile across key dimensions
• easy to understand model for DB
• easy to grok for freelancers wishing to update their model
• can be visualised easily

Disadvantages:

• may not satisfy granular requirements of projects for knowing when a freelancer is really available
• cannot really
• doesn't take into account holidays

[Jskobos]

So you're saying that there would be a field in the Freelancer table for each of the time periods? That could work.

This seems like it's going to be a major part of the UX for both freelancers and managers, so I think it's worth looking around to see if there are better tools available (e.g. maybe there's something useful in the Google calendar API, and we can store links to calendars in each Freelancer record).

[ortonomy]

@Jskobos - hmm, or rather a collection in one column with all of the relevant 'tags' for availability:

[mon-day,mon-eve,no-sun,no-sat] which we parse on the front end into a visual representation... potentially visualised as:

       MON TUE  WED  THU  FRI  SAT  SUN
morn   [x] [ ]  [ ]  [ ]  [ ]  [ ]  [ ]
after  [x] [ ]  [ ]  [ ]  [ ]  [ ]  [ ] 
eve    [x] [ ]  [ ]  [ ]  [ ]  [ ]  [ ]

Could be stored as a array of 7 3-tuple: [[0,0,0],[0,0,0],[0,0,0],...] - but I don't know if this is accessible, easily parseable or efficient.

I agree that there is almost definitely a proven model for this challenged by the big boys.

[ortonomy]

After discussion -- this is not necessary until later, and also will likely use a standard calendar format.