CODEWITHSUNDEEP
mysqlrelational-databasemysql-workbench
Andrew Fox
Andrew Fox

Reputation: 890

How do I properly structure my relational mySQL database

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.

Upvotes: 0

Views: 1214

Answers (2)

grossvogel
grossvogel

Reputation: 6782

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.)

Upvotes: 3

adhocgeek
adhocgeek

Reputation: 1457

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...

Upvotes: 1

Related Questions