Applying Single Table Inheritance

database-design

From that StackOverflow's question:

database structure

Summarizing:

  • My CRUDs are: customers, employees and branches.
  • Customers and Employees are associated with one Person, one Individual (fields related to the person itself) and one User (for login purposes). So Individuals and Users are always related to one Customer or Employee person.
  • Branches are associated with one Person and one Company (fields related to the corporate person). So Companys are always related to one Branch person. They didn't have an user because they're affiliated to the company that this database stands for – it's their employees who can authenticate into the app.
  • An Employee belongs to one Branch. A Branch has zero or many Employees. (yeah, diagram is incorrect, sorry!)
  • Currently I'm using a single bit to diff Employee role – I've employees with operational and administrative rights. Roles permissions goes in the code itself, and here you can realize that customers also have their own role… Any suggestions to implement a better (and simple) way to manage that roles for both customers and employees?

How should I change that wrong 3NF structure to achieve a well designed single table inheritance for a better use of MVC pattern?

Update

Ok, I tried to simplify it a bit, and now I accomplished this new structure:

new database structure

Can I still improve it? How?

Best Answer

Unless I'm misunderstanding your requirements, the Person, Individual, Company, and User tables could be combined into a single record since the relationships between these tables are all one-to-one. Unless this is some kind of trick question...

One could go so far as to say all the tables that involve one-to-one relationships could be combined into a single table since that would eliminate the duplicate foreign keys, thereby saving space. Having said that you'd really have to be hard pressed for space to worry about saving that small an amount of space.

It looks to me like you need to revisit your design spec with an eye for simplification.

Related Question