"I have a database for a school ... [with] numerous tables obviously, but consider these:

CONTACT - all contacts (students, faculty): has fields such as LAST, FIRST, ADDR, CITY, STATE, ZIP, EMAIL;
FACULTY - hire info, login/password, foreign key to CONTACT;
STUDENT - medical comments, current grade, foreign key to CONTACT."

"Do you think it is a good idea to have a single table hold such info? Or, would you have had the tables FACULTY and STUDENT store LAST, FIRST, ADDR and other fields? At what point do you denormalize for the sake of being more practical? What would you do when you want to close out one year and start a new year? If you had stand-alone student and faculty tables then you could archive them easily, have a school semester and year attached to them. However, as you go from one year to the next information about a student or faculty may change. Like their address and phone for example. The database model now is not very good because it doesn’t maintain a history. If Student A was in school last year as well but lived somewhere else would you have 2 contact rows? 2 student rows?  Or do you have just one of each and have a change log. Which is best?"

• Following the discontinuation of AllAnalytics, the links to my columns there no longer work. I moved the 2017 columns to dbdebunk and, time permitting, may gradually move all of them. Within the columns, only the links to sources external to AllAnalytics work. 

• @DBDdebunk on Twitter: will link to new posts to this site, as well as To Laugh or Cry? and What's Wrong with This Picture posts, and my exchanges on LinkedIn.
• @The PostWest blog: Evidence for Antisemitism/AntiZionism – the only universally acceptable hatred – as the (traditional) response to the existential crisis of decadence and decline of Western (including the US)
• @ThePostWest Twitter page where I comment on global #Antisemitism/#AntiZionism and the Arab-Israeli conflict.

“One table named PERSON. Anything else is a denormalized nightmare.”

“What if Professor of mathematics also takes courses in digital photography? Are you going to store her phone number in 2 rows, both in FACULTY and STUDENT tables? What if a postdoc student also has a part time job teaching? Depending on the platform, you might find materialized query tables aka materialized views and/or index covering to be good alternatives to denormalization. I'm speaking about DB2 and Oracle.”

“I don't see how having separate tables for fundamentally different types of contacts could be considered denormalization. In fact, your current scheme of having a bunch of 1:1 relationships seems to reflect entity-oriented thinking rather than relational thinking; it looks like your FACULTY and STUDENT tables and their 1:1 relationships are attempts to 'subclass' your 'entities'.”

“Some properties are formed as the disjunction of two or more properties called meaning criteria. Each meaning criterion (individual disjunct) induces a partitioning of a group into two subgroups of entities -- those that meet the criterion and those that do not -- and serves to differentiate each subgroup from the others. Some of the subgroups will be disjoint, while others will not. Each of the subgroups is defined by (“inherits”) (1) the defining properties of the group conjoined with (2) at least one meaning criterion (that meaning criterion, or those meaning criteria, thus becoming the defining property, or properties, respectively, specific to the proper subgroup).”

--David McGoveran

CONTACTS (ID,LAST,FIRST,ADDR,CITY,STATE,ZIP,EMAIL,...)
FACULTY (ID,HIREINFO,LOGIN,...)
STUDENTS (ID,MEDICAL,GRADE,...)

FACULTY (ID,LAST,FIRST,...,HIREINFO,...)
STUDENTS (ID,LAST,FIRST,...,MEDICAL,GRADE,...)

and includes a disjunctive (i.e., mutual exclusivity) constraint. A UNION view of two projections on the FACULTY and STUDENTS relations would represent the supergroup.

A true RDBMS (full support of relation predicates and a theory of types) would support ESS relationships. Users would specify the supergroup and subgroups and their properties and the DBMS would figure things out for itself (i.e., create the corresponding relations, the disjunctive constraint, the  CONTACTS view), and would take the ESS relationship into consideration in queries.

SQL DBMSs do not. They are unaware of the relationship -- it exists only in the mind of users. Are disjunctive constraints expressible in SQL? And recall that multi-relation views are not updatable.



Note: I will not publish or respond to anonymous comments. If you have something to say, stand behind it. Otherwise don't bother, it'll be ignored.

References

[1] Pascal, F., What Relations Really Are and Why They Are Important.

[2] Pascal, F., Normalization and Further Normalization Parts 1-3.


[3] Pascal, F.,  Don't Design Databases Without Foundation Knowledge and Conceptual Models.

[4] Pascal, F., The Conceptual-Logical Conflation and the Logical-Physical Confusion.

[5] Pascal, F., "Denormalization for Performance": Don't Blame the Relational Model.

[6] Pascal, F., Reader Mail Sets vs. Graphs, Education vs. Training.

[7] Pascal, F., Relationships and the Relational Model Parts 1-3.

[8] McGoveran, D., LOGIC FOR SERIOUS DATABASE FOLK (draft chapters), forthcoming.

[9] Pascal, F., The Principle of Orthogonal Database Design, Parts 1-3.