0

I am making a database that is for employee scheduling. I am, for the first time ever, making a relational mySQL database so that I can efficiently manage all of the data. I have been using the mySQL Workbench program to help me visualize how this is going to go. Here is what I have so far:

Mock Up

What I have pictured in my head is that, based on the drawing, I would set the schedule in the schedule table which uses references from the other tables as shown. Then when I need to display this schedule, I would pull everything from the schedule table. Whenever I've worked with a database in the past, it hasn't been of the normalized type, so I would just enter the data into one table and then pull the data out from that one table. Now that I'm tackling a much larger project I am sure that having all of the tables split (normalized) like this is the way to go, but I'm having trouble seeing how everything comes together in the end. I have a feeling it doesn't work the way I have it pictured, @grossvogel pointed out what I believe to be something critical to making this all work and that is to use the join function to pull the data.

The reason I started with a relational database was so that if I made a change to (for example) the shift table and instead of record 1 being "AM" I wanted it to be "Morning", it would then automatically change the relevant sections through the cascade option.

The reason I'm posting this here is because I am hoping someone can help fill in the blanks and to point me in the right direction so I don't spend a lot of hours only to find out I made a wrong turn at the beginning.

Andrew Fox
  • 794
  • 4
  • 13
  • 30
  • 1
    Do schedule AND employee need to refer to license_mat? Beside from that, where do you see your problems? Did you set up that database and played around with it? That might help you starting to work with relational databases. – Argeman Nov 14 '12 at 11:46
  • @Argeman no, you're right, it only needs to be referenced to the employee. I guess that's part of where my confusion is, I have updated my question to try to shed some more light on this. – Andrew Fox Nov 15 '12 at 03:01

2 Answers2

3

Maybe the piece you're missing is the idea of using a query with joins to pull in data from multiple tables. For instance (just incorporating a couple of your tables):

SELECT Dept_Name, Emp_Name, Stat_Name ...
FROM schedule
INNER JOIN departments on schedule.Dept_ID = departments.Dept_ID
INNER JOIN employees on schedule.Emp_ID = employees.Emp_ID
INNER JOIN status on schedule.Stat_ID = status.Stat_ID
...
where ....

Note also that a schedule table that contains all of the information needed to be displayed on the final page is not in the spirit of relational data modeling. You want each table to model some entity in your application, so it might be more appropriate to rename schedule to something like shifts if each row represents a shift. (I usually use singular names for tables, but there are multiple perspectives there.)

Community
  • 1
  • 1
grossvogel
  • 6,694
  • 1
  • 25
  • 36
  • I think you've just filled in a big hole for me, one of the things I couldn't figure out was how to pull the data from all the different tables. I think my head is going to explode. – Andrew Fox Nov 15 '12 at 03:55
  • It took me a day or two to really get my head around this and ultimately you summed it up well. It was difficult for me to see, but now I am confident I understand the path I need to take. Thanks again for the help :) – Andrew Fox Nov 16 '12 at 10:28
1

This is, frankly, a very difficult question to answer because you could get a million different answers, each with their own merits. I'd suggest you take a look at these (there are probably better links out there too, these just seemed like good points to note) :

http://www.devshed.com/c/a/MySQL/Designing-a-MySQL-Database-Tips-and-Techniques/

http://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_form

http://www.sitepoint.com/forums/showthread.php?66342-SQL-and-RDBMS-Database-Design-DO-s-and-DON-Ts

I'd also suggest you try explaining what it is you want to achieve in more detail rather than just post the table structure and let us try to figure out what you meant by what you've done.

Often by trying to explain something verbally you may come to the realisations you need without anyone else's input at all!

One thing I will mention is that you don't have to denormalise a table to report certain values together, you should be considering views for that kind of thing...

adhocgeek
  • 1,437
  • 1
  • 16
  • 30
  • Thanks for the input. I read through the links you posted and there was some good information in there. I should have mentioned that I am really comfortable in the mySQL arena in that I have used many databases in the past, but this is the first time I've had a need to normalize them and that is mostly what I'm getting tripped up on. Cheers. – Andrew Fox Nov 15 '12 at 03:25
  • I'm actually slightly horrified that this is the first time you've had to normalise a set of tables...in my experience, normalising is where you start and it's only when you need to optimise for specific queries that you end up looking at de-normalising. – adhocgeek Nov 15 '12 at 14:08
  • That is a totally reasonable thing to say, I actually thought the same thing once I started to do this. However most everything I've done so far has been on a very small scale and really only needed one table. Sounds crazy but it happens :) – Andrew Fox Nov 16 '12 at 10:25