3 ms·

Two points here just for fine grain discussion: 1. The first table structure is a flat non-normalized table structure that trades normalization for easy to que

by gls2ro 2y ago

Two points here just for fine grain discussion: 1. The first table structure is a flat non-normalized table structure that trades normalization for easy to query and select computed properties 2. Second structure is a normalized table structure that trades the normalization for joins.

PaulHoule 2y ago
Either one is normalized so far as I know. It is easy to write a query for the first that gets a list of students names and the dates they applied. That query is harder for the second one. On the other hand figuring out what state a user was in at time t could be a very hard problem with the first table. My experience with the first is that you find corrupted data records, one cause of that will be that people will cut and paste the SQL queries so maybe 10% of the time they wind up updating the wrong date. Systems like that also seem to have problems with data entry mistakes. The biggest advantage of #2 is ontological and not operational, which is that in a business process an item is usually in exactly one state out of a certain set of possible states. Turns out that this invariant influences the set of reasonable requirements that people could write, the subconscious expectations of what users expect, needs to be implicitly followed by an application, etc. Granted some of the dates I listed up there don't quite correspond to a state change, for instance the system needs to keep track of when a student started an application and when the last document (transcripts, letters, etc.) has been received. With 5 documents you would have 32 possible states of received or not and that's unreasonable, particularly considering that a student with just one letter and a very strong application in every other way might get accepted despite that. It's fair to say the student can have an "open application" and a "complete application". Similarly you could say the construction of an airplane or a nuclear power plant can be defined by several major phases but that these systems have many parts installed so if the left engine is installed but the right engine is not installed these are properties of the left and right engine as opposed to the plane.

by gls2ro 2y ago

PaulHoule 2y ago

Either one is normalized so far as I know. It is easy to write a query for the first that gets a list of students names and the dates they applied. That query is harder for the second one. On the other hand figuring out what state a user was in at time t could be a very hard problem with the first table. My experience with the first is that you find corrupted data records, one cause of that will be that people will cut and paste the SQL queries so maybe 10% of the time they wind up updating the wrong date. Systems like that also seem to have problems with data entry mistakes. The biggest advantage of #2 is ontological and not operational, which is that in a business process an item is usually in exactly one state out of a certain set of possible states. Turns out that this invariant influences the set of reasonable requirements that people could write, the subconscious expectations of what users expect, needs to be implicitly followed by an application, etc. Granted some of the dates I listed up there don't quite correspond to a state change, for instance the system needs to keep track of when a student started an application and when the last document (transcripts, letters, etc.) has been received. With 5 documents you would have 32 possible states of received or not and that's unreasonable, particularly considering that a student with just one letter and a very strong application in every other way might get accepted despite that. It's fair to say the student can have an "open application" and a "complete application". Similarly you could say the construction of an airplane or a nuclear power plant can be defined by several major phases but that these systems have many parts installed so if the left engine is installed but the right engine is not installed these are properties of the left and right engine as opposed to the plane.