From that StackOverflow's question:
Summarizing:
- My CRUDs are: customers, employees and branches.
Customer
s andEmployee
s are associated with onePerson
, oneIndividual
(fields related to the person itself) and oneUser
(for login purposes). SoIndividual
s andUser
s are always related to oneCustomer
orEmployee
person.Branch
es are associated with onePerson
and oneCompany
(fields related to the corporate person). SoCompany
s are always related to oneBranch
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 oneBranch
. ABranch
has zero or manyEmployee
s. (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:
Can I still improve it? How?
Best Answer
Unless I'm misunderstanding your requirements, the
Person
,Individual
,Company
, andUser
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.