One table for all persons

database-design

i have one table for all person (with fields ex. name, last name) because each person can has one or more role (customer, lead/prospect, supplier, employee).
And i have tables (customers, leads, suplliers, employees) for each role with custom fields.
Also I have a roles table and a table for person and his roles.

Questions:

Is correct save leads/prospects in the same person table? Because i can get thousands of leads. But this can down sql query performance?

Other question, some person (customer and employee) can be a user (can be login). Then i create other table named users, but the email is saved in person table. I can use a username for login but i want too the users can login with email.

What do i?
I use only use username (in user table), other option copy email (from person table) to user table or for last option a query using the two tables: person and user?

Thanks

Best Answer

You must divide what value is an attribute for what entity.

For example, Name is an attribute of a Person, so it is stored in Person table, and when you need the Name for some Employee you'll join these tables and get the Name by reference (i.e. there is no Name field in Employee table). But when you need Employee position you will take (and store) it from Employee table because the position is not an attribute of Person, but Employee.

some person (customer and employee) can be a user (can be login)

The same.

If the login does not depend on whether the Person is an Employee or a Customer (i.e. after logging in the Person may perform actions which belongs to both/either as Employee and/or as Customer), it is an attribute of Person and is stored in the Person table.

But if a certain login is the login of the Employee only (can perform Employee actions, but cannot be used by the Customer and perform Customer actions) - it is not an attribute of Person, but Employee, and is stored in Employee table.

Related Solutions

Applying Single Table Inheritance

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.

Separate or Combine 2 Tables with Similar Attributes

I'd say "Customer" and "Employee" are two roles a person can play in your system. Thus, I'd add concept of "Role" to your design. Then you can store customer related attributes in one table, and employee related attributes in another. You will have something like

User (user_id, name, ...)
Role (role_id, description)
UserRole (user_role_id, user_id,role_id, date_from, date_thru) 
-- note, dates are quite important(at least date_from should be not null)
CustomerRoleAttribute(user_role_id, NewsUpdateSubscription)
EmployeeRoleAttribute(user_role_id, salary)

Also, to be absolutely correct, Customer and Employee roles require second part - for instance, I'm a customer of AAA company, and an employee of BBB, so my role is defined by relationship of certain type with other user (which is organization in case of "Employment" relationship).

You can find out more about this approach in "The Data Model Resource Book" by Len Silverston

Best Answer

Related Solutions

Applying Single Table Inheritance

Separate or Combine 2 Tables with Similar Attributes

Related Question