Normalising Customers and Employers with a people table

Question

At what normalisation stage is it recommend to split say Customers and Employees into a People table and then link to the unique attributes of Customers and Employees? I can't seem to find the Functional Dependency to justify the change. This makes sense in OOP where you use inheritance but I don’t understand why it is done in database schemas.

This is an example of what I mean, possibly with more unique attributes:

Customers: Cid, FName, LName, AddId, CustType    
Employees: Eid, FName, LName, AddId, EmpType

vs

People   : FName, LName, AddId    
Customers: PeopleId, CustType
Employers: PeopleId, EmpType

The current answers explain why it is recommended to move data to a People table; however, at what normalisation stage does it become a FD to justify creating the extra table?

Answer 1

At what normalisation stage is it recommend to split say Customers and Employees into a people table and then link to the unique attributes of Customers and Employees.

The short answer here is none. Normalization, more formally called Projection-Join Normalization, is a scientific process in which one can remove redundancies in R-tables due specifically to join dependencies which are not implied by the candidate keys. The join dependencies are exploited by taking projections based on them to create two or more tables from the original table which removes the redundancy. It is important to note that normalization cannot remove all redundancy. Instead, it can remove only redundancies caused by join dependencies not being implied by the primary key.

I cant seem to find the Functional Dependency to justify the change? This makes sense in OOP where you use inheritance but I don’t understand why it is done in database schemas

In the scenario of customer / employee vs. person / customer / employee the decision as to which to use is based on the subjective perceptions of the business domain. There is no scientific basis to choose one approach over the other. Describing the inheritance approach as "makes sense" the subjective nature is exposed. It makes sense to you, and perhaps to me, but perhaps not to someone else. This is an example of creating a business model at the conceptual level and describing entity types. Either approach can be mapped to R-tables at the logical level which are fully normalized. This is why you cannot find the specific functional dependency which justifies the change - there isn't one.

A good primer on normalization is Fabian Pascal's Practical Database Foundation Series, specifically paper number one which describes business modeling and paper number two which describes normalization. Chris Date's Database Design and Relational Theory provides an in-depth look at normalization along with orthogonality with respect to reducing redundancy in logical database designs.

Answer 2

The design of your tables is not, as you noted, an OOP inheritance. The underlying model is simpler:

• Do not duplicate your data!

The more data you have, the more important this is because:

1. Nobody will type the duplicate data exactly the same in all cases. (Names, Addresses, etc.)
2. If you have entered similar data in two different tables, you will regularly need to do some Sherlocking to figure out who is whom.

Having said that, people identification (particularly customers) is difficult to get correct and consistent no matter how diligent you try to be. Likely you will unwittingly enter some people a second or third time. But a People table gives you one place to examine, which can be a bit of a help.

Creating a Persons table would be 2nd Normal Form.

I do not really understand what issue you are struggling with when you ask: when it "becomes an FD to justify creating the extra table". You are basically making a choice between:

1. Choosing to just cope with variant and duplicated data.
2. Set up your data to avoid duplicated data as much as possible.

I avoid duplicating data as much as possible, because it gives me fewer headaches over time.

Answer 3

If you change it you may over-complicate the design. You'll also end up with more rows to scan through for the table people, when performing a join on that table

To me it doesn't make sense to use oop design techniques for databases