Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

4 Answers 4

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.

share|improve this answer
1  
I think company and individual are both mutually exclusive subsets of person, and same with customer and employee. At least that's how I read his schema. –  Chris Travers Oct 3 '12 at 4:38
add comment

Ok, so what I see here is you are tracking person data, which is joined to customers and employees so with additional information there. Rethink your mappings to branches. It looks to my eye (not knowing what you are trying to do) like you need to do a 1:many mapping of branches to persons. I don't know why you'd have a separate mapping to employees.

The next thing I would look at doing is encapsulating that storage inside updatable views for your MVC. This means instead of redesigning your storage, you are encapsulating normalized storage inside of denormalized interfaces. The MVC then expands the data model it is given through these views, but the storage logic is in your database. This promotes data re-use. How you do this part will be RDBMS specific however.

share|improve this answer
    
I've updated my question almost at the same time you answered... Yeah, branches are 1:many to costumers in the previous diagram, I've used a wrong cardinality, sorry! I tried to simplify the database structure in the last diagram, now I think it's more easy to understand what I need... :) –  Paulo Freitas Oct 3 '12 at 4:45
add comment

My thinking is as follows:

a) I would keep all person information in a single table whether they are customers or employees - including login information

b) Create a customer table with a link to Person, and details of when they became a customer, when they stopped being a customer, and other customer specific information

c) Create a table for employee to track employee related information

d) Create a table employeebranch so that you track the start and end information for the employee in a branch, add a column for personid so that you can easily when the person was assigned to a branch, start and end date for the assignment to the branch

e) In real life, a person may have different contact information as a customer or an employee - you can create a contact table, add a contact type - for person, customer and employee, start date and end date (addresses get invalidated)

f) Create a table roles where the roles are defined

g) Create a personroles table so that you can assign one or more roles to a person, this is irrespective of whether they are customers or employee (as the rules become more complex) it will make more sense - this is how Role Based Access (RBAC) works

share|improve this answer
add comment

I think the problem is that the tool you use to present your design can not express what you want to tell us. From the title of your post i think that you want to express some "is-a" relation between the entity "person" and the entities customer,employee, branch user,individual and company. But this inheritance-/isa- relation is not expressed by your diagrams and therefore the result is a lot of confusion. You took the input from this confusion and created this role based approach. I think you should solve your initial problem: express your ideas by an adequate diagram and discuss your ideas using this diagram.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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