Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am researching database design and theory and have finally made a thoughtful attempt at creating a schema that would be based upon SQL. I would like, if someone has time, to review my layout and make sure that it is logical. Tips and criticism aimed at bettering my knowledge is of course welcome! :)

Here is my general goal: Create a database that will allow for a business with multiple stores to be able to track employee training progress by simply marking date trained and date verified. Basically, this would be the hierarchy:

Business 1 
--Store Location (a)
----Employee
----Employee
----Employee
--Store Location (b)
----employee
----employee
----employee

Business 2
--Store Location (a)
----employee
----employee
--Store Location (b)
----employee
----etc...

This schema should allow for unlimited businesses, stores, and employees.

As far as the training goes, there should be this type of hierarchy:

Business 1
--Employee Class           (Manager, Employee, Salesman, Associate, etc)
----Training Category      (Sales, Stocking, Manufacturing, etc)
------Training Activity    (How to sell X product, How to stock Y aisle, etc)
------Training Activity
------Training Activity
----Training Category
------Training Activity
------Training Activity
--Employee Class
----Training Category
----Training Category

Business 2
--Employee Class           
----Training Category     
------Training Activity    
------Training Activity
------Training Activity
----Training Category
------Training Activity
------Training Activity
--Employee Class
----Training Category
----Training Category



The following things should be variable based on business needs and created upon creation of business's account:

 * Number of Stores
 * Number of Employees 
 * Employee Classes
 * Training Categories
 * Training Activities

So the grand questions are:

  1. How close did I get?
  2. How can it be done better?

Here is the Diagram (http://i.stack.imgur.com/V38cr.jpg) : enter image description here

share|improve this question

2 Answers 2

up vote 1 down vote accepted

You need to study database normalization. There are many cases in your design where you are carrying company_id for example, where this is a transitive dependency.

You could simplify your design by structuring it this way:

ERD

Note that there are many fewer relationships in this design than in what you proposed. This is possible (and desirable) because you are able to traverse relationships across other entity types to get to higher level ancestors. For example you know what company an employee works for by knowing which store they work at.

share|improve this answer
    
Thanks for your input! :) I will look into normalization again and make sure I truly understand it. :) –  Cory Scott Cline Nov 6 '12 at 12:18
    
This may sound silly, but what tool did you use to create this diagram? I like the "hand-drawn" style! –  FrustratedWithFormsDesigner Nov 6 '12 at 16:42
1  
@FrustratedWithFormsDesigner - Not a silly question. I get it fairly often actually. I used Visio with ERD smartshapes that I built to use the Martin graphical conventions, which is what I like to use. You could use any ERD shapes that suit your taste. The hand-drawn effect comes from using a custom line pattern that is "squiggly". I use it for quick and dirty or preliminary designs. I find people are less likely to treat a design as carved in stone if it looks hand-drawn. –  Joel Brown Nov 6 '12 at 21:50
    
As an update to all who care, I went and spent a few days learning Normalization and I went back to look at my original design and almost puked lol. Thanks for the advice, and I feel a lot mor confident in my ability to create a schema worth having :) –  Cory Scott Cline Nov 9 '12 at 19:28

The picture was useful. I think you did well.

I don't think of things as hierarchies, where stuff like Category gets in the way. I make tables for the nouns (users, activity) and then adjectives become columns (category, class).

You use the word 'class' for 'employee type' or 'employee group'. In a training system like this, I would have expected the 'class' table to store your 'activities'. If your table names match the nouns that your end-users are using, it'll be easier in the long run.

My comments:

  • Give training_activities its own id, and use that from training_tracker.
  • An employee (a single employee profile) can't move from one company to another nor work for two companies at the same time.
share|improve this answer
    
I see what you mean with the second comment. I will look into ways to solve that :) And your bit about nouns and adjectives was one of the most useful bits of advice I have read yet :) Thanks!! –  Cory Scott Cline Nov 6 '12 at 12:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.