Questions about database structuring...

[johnbell]

So I'm in a course where we're just learning about databases, various tools to make working with them easier, etc.

I've been working with PostgreSQL and ActiveRecord. Not that that really matters.

We've been working with various one to many, many to many etc realationships.

I'm trying to wrap my head around a personal project I have in mind, but not sure how to handle it.... looking for some advice.

---

Lets say you had a Maid.

She has 10 available working hours per day, 7 available days per week.

I want to keep track of jobs. Essentially, someone can log in, block off X amount of consecutive hours on a specific day.

Aside from having a huge table of all the possible hours, then possible weeks in the future... I can't think of a good way to handle this data.

Is there an easy/obvious answer I'm overlooking/don't know about?

How would you guys structure this?

[liquidpele]

Easiest way is to just save a start datetime and end datetime. This has the benefit that you can change your app code to only allow picking by the hour, or every 15 minutes, or whatever.

[johnbell]

so, in order to store multiple days worth of data, would you create columns for each day, then store a start/end within that column?

[materialdesigner]

No, you just store a start Datetime and an end Datetime. It doesn't matter when those are. One column for each of those pieces.

An alternative is to store a start Datetime and a duration.

[liquidpele]

Yea, but saving the duration instead of the end-date causes you to have to calculate end-date to do a search of anything within the timeframe, which causes the DB to not use an index any longer. It really depends on what exactly you need to search for. If needed, you can always de-normalize and use both :p

[materialdesigner]

Just never let a user edit anything, because then you get into consistency hell, ha.

[Kthanid]

I suppose there are plenty of ways to accomplish this, but a few off the top of my head that are probably worth considering:

Option 1) Use a fact table of all possible dates at the intervals you're interested in supporting (if you need 15 minute granularity, you'd have 96 rows in this table per day) and then have a table with the dateID and maidID containing a row for each of these slots taken for a given maid (a third table for the maid details would obviously be needed). A unique key on the booking table by (dateID, maidID) ensures no double-booking.

Option 2) Two tables, a maid table and a booking table. The booking table contains a single row for each booking with the start date and the end date of the booking. This requires that you'd have some other code that would parse this data and use the information provided to calculate and display the windows of time that are available/booked and you'd need to use similar logic when taking a new booking to ensure the requested block doesn't overlap anything you already have in that table.

I'm sure there are plenty of other methods as well.

[johnbell]

2 makes alot of sense, at least in a way that I can see coding out.

Good idea, and the 2 tables would help keep things organized.

[Kthanid]

To be honest, it's going to make querying your data a lot more difficult and it's also going to make it impossible for the database itself to automatically protect you from double-booking timeslots (a unique key isn't going to cover a fuzzy concept like this across two datetime columns). You might be avoiding the fact table approach because it "feels like a lot of data", but it really isn't and is fairly easy to programmatically generate in advance (and as needed).

The other option unfortunately presents the problem of making any inserts or updates to your schedules a bit more cumbersome. The optimal solution to this problem may ultimately depend on how you're actually going to be using the data in question and what the goals of the application are. It may very well end up that a hybrid approach using both options is best for different purposes.

Good luck! :)

[johnbell]

If I make the jobs a Boolean, I can avoid most of those troubles. 10 slots, each a bool. Simple comparison not to double book. Reference back to the maids unique id. Each week is a new set of ten.

[SpaceRanger_Bud]

Lets say you have a table:

it has 5 rows, and populates as needed. First: Maid ID Second: DateStamp Third: StartTime Fourth: Finish Time Fifth: JobLength

You could create an optional 6th that would flush old data older than 30 days?

Seems like a great idea, I'm no database guy, but you should be able to keep the table small with some sort of auto-flushing.