Reviewing a First Thought-Out Attempt at a Database Schema

database-designschema

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

Best Answer

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.

Related Question