Sunday, January 29, 2017

This Week

1. What's Wrong with This Database Picture?

"I have a database for a school ... [with] are numerous tables obviously but consider these:
CONTACT - all contacts (students, faculty) has fields such as LAST, FIRST, MI, ADDR, CITY, STATE, ZIP, EMAIL;
FACULTY - hire info, login/password for electronic timesheet login, 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?" --comp.databases.theory

I have been using the proceeds from my monthly blog @AllAnalytics to maintain DBDebunk and keep it free. Unfortunately, AllAnalytics has been discontinued. I appeal to my readers, particularly regular ones: If you deem this site worthy of continuing, please support its upkeep. A regular monthly contribution will ensure this unique material unavailable anywhere else will continue to be free. A generous reader has offered to match all contributions, so please take advantage of his generosity. Thanks.

2. Quote of the Week

"The oldest method of storing data in a relational database was developed by E.F. Codd and focuses on ensuring that data is physically stored in as efficient method as possible. This methodology requires data to be stored in what is known as 3rd normal form (3NF)."

3. To Laugh or Cry?

Data Modelling

4. Of Interest

5. Now available



And now for something completely different

New at The PostWest: The Distortion of Capitalism--the Corporate Welfare State--as Socially Destructive as the Distortion of Communism

My Take of the Week
That the parasitic class that extracts wealth from society without creating any is promoting machine over human decision on grounds of algorithmic rationality vs. human irrationality should alone make it suspect. The tech industry's preference for uninformed, uneducated youth over old age knowledge and experience exposes the machination (pun intended) for what it really is.

Anti-semitism? Nah, just "criticism of Israel's policies".

    PinchMe of the Week
    Obama names guru of Iran deal and abstention in UN anti-settlements resolution to Holocaust Memorial Council

    Article of the Week

    Martin Kramer: In the words of Martin Luther King

    Video of the Week

    Anti-Semitism: The Longest Hatred

    Site of the Week

    IMRA - Middle East News & Analysis

    Book of the week


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


    1. I see at least 2 things wrong with that picture. First there is no conceptual model presented upon which to derive a logical model. Second, the logical model options being discussed (i.e. 1 table, 3 tables) are based not on a conceptual model but instead on unquantified performance considerations which, even if quantified, still belong at the physical level. This is a good example of the logical physical confusion (LPC) you write about that many of us practitioners suffer from and slip into without realizing it.

      On the bright side at least the writer has started asking some questions that should be directed toward the client to start the process of creating a conceptual model.

      Quote of the week is great. "Ensuring data is *physically stored*", and "requires data to be *stored*". I see why we should be shaking our heads. The RM is a logical model and leaves the physical storage to be defined by the implementor in any way they see fit so long as the logical model of presenting data only as relations is not violated.

      Looking forward to reading your explanations of what was wrong with the picture and continuing to learn from those.


    View My Stats