2

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 (https://i.sstatic.net/V38cr.jpg) : enter image description here

2 Answers 2

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:

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.

1
  • 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 :) Commented Nov 9, 2012 at 19:28
1

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.
1
  • 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!! Commented Nov 6, 2012 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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