Applying Single Table Inheritance

Question

From that question:

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?

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

Can I still improve it? Where? How would you model that?

Answer 1

Creating a new table for city is only worthwhile if you have normal-low cardinality (fewer unique / more repeats). So depending on your users, you will have to decide on your own. On the other hand, you can break away Address table and phone number table.

Depending on what kind of information you are storing about employee and customers, you can have just a field indicating if the person if employee or customer or can have two separate tables.

If we talk about roles, they usually denotes the authority / position / accessibility of an employee. I.e. Regular, P/T, Manager. You can develop the access levels & visibility of the content for each group / role.

Take out all personal details from Person and store them in a new table. (Just curious.. DO you REALLY need nickname field? Disk I/O is one of the largest bottleneck of any application) separate personal details from professional details and IDs.

Talking about Branch, are you sure that there will be only one contact at any time? Usually, that is not the case in the business world. But a branch / person will have ONLY ONE address at any given time, so here your separate address table will be helpful.

It's really hard to suggest a structure in general. A good design is always based on the business rules.