2

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?

  • How do you identify in the 1st design a person who is both a Customer and an Employee? In other words, where is the People entity in the first design? – ypercubeᵀᴹ Feb 18 '15 at 20:38
  • @ypercube This is a good point. The next question I’m struggling with is at what stage in normalisation does it become an FD to justify creating the extra table? – user59821 Feb 18 '15 at 21:52
  • It is not a normalization similar to not mixing warehousing and inventory. People even may be employees multiple times (quite then get rehired). And it is not a people table - it is an Entity table. Because your customer may well not be a person but a corporation. – TomTom Apr 4 '16 at 8:16
  • I wouldn't think of this design decision as something that occurs in any "stage". When first designing the application, identifying at least two tables that will contain mostly the same information is a red flag that you need to push for normalization to begin with. – Greg Burghardt Apr 4 '16 at 12:57
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.

| improve this answer | |
1

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.

| improve this answer | |
0

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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