Database Table Design For a Work Project

[ScottieF]

Bold Text HereHi there I'm just wondering what the best or a good table layout would be for a little project I'm trying to do at work. I work at a temp to perm agency where companies use us for the probationary peroid of employement and then hire the workers on once they have completed the necessary amount of hours at their facility. So I'm trying to set up an online application signup and management system for our jobs and employees.

So basically we have many employees and many jobs and I'd like to store information about each employee and then create jobs that I can assign to them. The part I'm having diffictulty figuring out the best layout for is keeping track of their hours. So once I have an employee and a job set up I want the employee to be able to be assigned to that jobs and each week keep track of their hours for that job. Sometimes an employee may not workout at a certain location so they get moved to another job mid week and will have essentially have two jobs for that one week.

I was thinking about having an employee table, a job table, and then a employee_job table that has a foreign key to an employee and a job. In the employee_job table there would be a one to many link with a weeks table which would just hold how many hours the employee worked that week for that specific job. Is this a good solution or is there a better way to manage all of this?

[gusano79]

I was thinking about having an employee table, a job table, and then a employee_job table that has a foreign key to an employee and a job.

This is a pretty standard way of doing a many-to-many relationship in a relational database; it should work for you.

In the employee_job table there would be a one to many link with a weeks table which would just hold how many hours the employee worked that week for that specific job.

I'm wary of representing weeks directly in the data; it feels too constrained. Depending on your requirements, doing it by week may be just fine, but in general, I'd suggest relating hours to a specific date. This allows you to chop the data more freely (e.g., start the week on Sunday vs. Monday in some cases), and frees you from having a custom definition of "week" (makes your system harder to maintain).

[ScottieF]

Ahh I ya actually the date is instead of weeks broken into days is a way better idea.

So I'd have a table that has an employee, the job he is at, a date, and the hours they worked that day.

Like this?

Employee | Job | date | hours
John Doe Job A 10-05-15 10
John Doe Job A 10-06-15 10
John Doe Job A 10-07-15 8.5
John Doe Job A 10-08-15 8
John Doe Job A 10-09-15 9
John Doe Job B 10-12-15 8.5
John Doe Job B 10-13-15 8
John Doe Job C 10-14-15 9
John Doe Job C 10-15-15 9
John Doe Job C 10-16-15 9

It seem a little weird to me to have so many rows being created with the same Employee and Job columns but I can't think of any other way to store them in a database. And I know my project isnt that big but for huge projects with a method similar to this it looks like it could be creating an insanely huge number of rows every day. Is mySQL extrememly fast at pulling out data from billiions or possibly even trillions of entries and is this the right way to tackle a problem like this?

[DenisOxon]

That looks fine, but I would use employee number/id, rather than his/her name.

[gusano79]

So I'd have a table that has an employee, the job he is at, a date, and the hours they worked that day

Yep.

That looks fine, but I would use employee number/id, rather than his/her name.

Also yep. Make that a FK back to the employee table.

It seem a little weird to me to have so many rows being created with the same Employee and Job columns but I can't think of any other way to store them in a database.

I can't think of anything simpler/cleaner either. This is fine; data models can often look strange coming from a more conceptual perspective.

And I know my project isnt that big but for huge projects with a method similar to this it looks like it could be creating an insanely huge number of rows every day.

It certainly could... but you're going to need this data one way or another, so step one is to do something. I think we've ended up at a good place for your smaller project. I wouldn't worry about scale unless you actually notice a storage or performance problem, and in the latter case, make sure you profile/measure the problem area to make sure you know exactly what's slowing things down before you make any changes.

I think we've left it at a good place for now. The data model is reasonably simple and understandable, and it gives you some flexibility in how you build the app. If changes are ever necessary, you'll be looking at a tradeoff of some sort--probably adding complexity and/or reducing flexibility to get the improvement you need.

Is mySQL extrememly fast at pulling out data from billiions or possibly even trillions of entries...

This will in part depend on your physical model, for example, wise selection of keys and indexes. You'll eventually run up against a performance wall, but I don't know what the practical limits are for MySQL.

...and is this the right way to tackle a problem like this?

I think it comes down to requirements. A simple RDBMS solution like this is fine, as long as it performs well enough. If you encounter problems later, it's perfectly normal to look at alternatives, like switching to a "big data" back end. Just remember to separate your data access from your business logic...

I wouldn't start with big data solutions unless you know up front that you'll have to manage a gajillion records.

[JOSheaIV]

To be honest, this is pretty much how I would have built the table. One for the Employee, one for the Job, and then one to record each employee for each job. Now for me though I would have probably setup an AUTOINCREMENT type field for both Employee and Job (or some other single column Unique ID). That way you can alter an employee or job, say the name, or a description, ext, and not have any effect on your third table.

Also, that table might be huge, but that's also probably the best design. Maybe you need to periodically flush it clean, or have some service back the data up somewhere and then remove those records.

I also say the size is fine because at my current job, there are tables like this. ERP systems can have hundreds and hundreds of tables, and some of them can grow rather massive (we had one client we had to place an index on the table because it took SO long to query there were probably 100s of thousands if not millions of records stored ... and that was one, I know of another that had a table with over 2 million for sure in it, used to track data flow).

By the way that is something to think up, setting up an index for the table. Maybe us Employee, Job, and Date, or Employee and Job (remember a PK can serve as an index too).