Database Design – Why Not Have Persons for Every Person in Schema?

Regardless of platform, the following remarks apply.

(-) Nested views:

• are harder to understand and debug

  e.g. What table column does this view column refer to? Lemme dig through 4 levels of view definitions...

• make it harder for the query optimizer to come up with the most efficient query plan

  See this, this, this, and this for anecdotal evidence. Compare to this, which shows that the optimizer is often smart enough to correctly unpack nested views and select an optimal plan, but not without a compilation cost.

  You can measure the performance cost by comparing the view query to an equivalent one written against the base tables.

(+) On the other hand, nested views let you:

• centralize and reuse aggregations or business rules
• abstract away your underlying structure (say, from other database developers)

I've found that they are rarely necessary.

In your example you are using nested views to centralize and reuse certain business definitions (e.g. "What is an eligible student?"). This is a valid use for nested views. If you are maintaining or tuning this database, weigh the cost of keeping them against that of removing them.

• Keep: By keeping the nested views you incur the advantages and disadvantages enumerated above.

• Remove: To remove the nested views:

  1. You need to replace all occurrences of the views with their base queries.

  2. You must remember to update all relevant queries if your definition of eligible student/teacher/school changes, as opposed to just updating the relevant view definition.

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.