Please review my first thought-out attempt at a database schema

Question

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

Answer 1

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:

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.

Answer 2

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.