hel designing a DB

h4x0rmx

I'm planning on creating a system that handles the schedules of several employees. They're divided into teams and their shifts change from one day to another, but they're the same week by week.
The schedules change every 6 months or so (the employees get rotated from one team to another). There are employees that work overnight shifts. So I have to take all these cases into account.

The current system was written on PHP3. The way they store the data on the DB is by inserting a record for every single shift of each employee. If an employee needs to change his/her shift in one of the days, that shift is deleted and then another one is added.
This type of design can be pretty hard to maintain as there has been a lot of customizations added.
(By the way I'm planning on witting my own system instead of implementing an existing one since there are a lot of other functions that need to be added besides the calendar... and I have the time to do it 😉 )

I was just wondering if any of you have had any experience with a system like this? I first want to focus on the design of the DB, because a good design will save a lot of time.

I'm thinking on emulating something like gCalendar (of course a lot more simple)....
I was thinking on saving the general info in something like:
user n1 works on mon/wed/fri from 11:00 to 18:00 and this schedule applies from 2009-02-01 til 2009-06-15 (one row in the db!!!)
and if there's a need to change the schedule a date just have exceptions on another table in which the default time schedule gets overridden and another one is stored just for that date (it could be that he needs to work all weds from 12:00 to 19:00 from 2009-04-01 to 2009-05-15)... this is just an idea, there are probably better ways to do it, any ideas?

Does anyone know of a tutorial or a book or something that can help me organize and design the DB?
Any ideas will be appreciated!

bpat1434

I don't know of any better tool for designing a database than good old pen and paper (or pencil and paper if you think you'll make mistakes 😉 ).

I know Microsoft makes a workflow tool that can be used for databases, and some database management tools come with a UML designer. But really all you need is a good UML tool. Eclipse has a plugin (or a couple) but they may be proprietary (i.e. $$). So like I said, pencil and paper 😉

All you need to do is sit down and think logically about how it will work. You know you'll have users and user information, and you'll have schedules for each user. You know each user is a part of a team, and that team works the same schedules (with minor exceptions).

Well right off the bat I see 3 tables (users, teams, schedules). I would set them up something like this:

+-------------------------+                +-------------------+
| Users                   |                | Teams             |
+-------------------------+                +-------------------+
| <> id (int)             |--+         +-->| <> id (int)       |
|    username (varchar)   |  |         |   |    name (varchar) |
|    password (varchar)   |  |         |   +-------------------+
|    first_name (varchar) |  |         | 
|    last_name (varchar)  |  |         +---------------------------------+
|    email (varchar)      |  |                                           |
|    created (timestamp)  |  |             +-------------------------+   |
|    modified (timestamp) |  |             | Teams_Users             |   |
+-------------------------+  |             +-------------------------+   |
                             |             | <> id (int)             |   |
                             |             | [] team_id (int)        |---+
                             +------------>| [] user_id (int)        |
                                           |    starting (timestamp) |
                                           |    ending (timestamp)   |
                                           +-------------------------+

Now you can visually see how users and teams match up. Adding schedules in there is as easy as:

+-------------------------+                +-------------------+                +------------------+           +-----------------+
| Users                   |                | Teams             |                | Schedules        |           | Times           |
+-------------------------+                +-------------------+                +------------------+           +-----------------+
| <> id (int)             |--+         +-->| <> id (int)       |--------+   +-->| <> id (int)      |     +---->| <> id (int)     |
|    username (varchar)   |  |         |   |    name (varchar) |         \  |   | [] time_id (int) |-----+     |    day (enum)   |
|    password (varchar)   |  |         |   +-------------------+          \ \   +------------------+           |    start (time) |
|    first_name (varchar) |  |         |                                   \ \                                 |    end (time)   |
|    last_name (varchar)  |  |         +---------------------------------+  \ \                                +-----------------+
|    email (varchar)      |  |                                           |  |  \
|    created (timestamp)  |  |             +-------------------------+   |  |   +--------------------------+
|    modified (timestamp) |  |             | Teams_Users             |   |  |                              |
+-------------------------+  |             +-------------------------+   |  |   +----------------------+   |
                             |             | <> id (int)             |   |  |   | Schedules_Teams      |   |
                             |             | [] team_id (int)        |---+  |   +----------------------+   |
                             +------------>| [] user_id (int)        |      |   | <> id (int)          |<--+
                                           |    starting (timestamp) |      |   | [] schedule_id (int) |
                                           |    ending (timestamp)   |      +-->| [] team_id (int)     |
                                           +-------------------------+          +----------------------+

Now you have a modular way to connect schedules to teams and users to teams. You can even be extremely finite down to days and times.

Now every user belongs to a team and each team has a schedule. Each schedule has many days. When a user wishes to modify their time, you need another table: users_times 😉 A user will create a new time that will be linked back via that join table users_times. So if they wanted to modify all Wednesdays they could do so, and not impact the global schedule for their team. You would only have to modify one row to do so. Or you could create a second table that mimicks the times table and call it custom times (or something) and store with it if it is repetitive or just a single occurance on a single date.

I don't know of any tutorials for this type of stuff, but I've given you my general thought process. I'm sure some other developers will think of other ways (some better, some worse) to tackle this issue. I'm a fan of keeping things clean and precise and not actually deleting data (but rather archiving it for records).

Hope that helps you out.

I'm also moving this to the Database forum as it seems it would fit better (and get more appropriate replies) there.

h4x0rmx

@
I appreciate your help and the time you took to answer my question.
I have some experience designing DBs, but not so much on working with this kind of system... like I wouldn't have thought of having the "starting" & "ending" fields on the Team_Users table, but I understand how they'd be helpful.

If other users want to help me, I'd just like to get that kind of ideas... like stuff that I should consider when designing the tables and how they relate to each other.

Thanks again!