One table for all persons

Question

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

Answer 1

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.