Interesting. I've always just followed the principle that if a parent can own more than one value for a particular item, you put that item in a child record, and keep the child records in a separate table and JOIN it as needed.
So, can a Consumer be both Approved and Pending?
By your logic, if a Company Name on a company record could change (when Acme Widgets is taken over by Apex Amalgamated) that should be in a child table, with a date range for when each name is effective.
If the status is always only one value at that point in time, but may change over time, you might store the CURRENT STATUS in the Consumer table, and an history (or Audit Trail) of updates to that status in a Status Audit Trail table.
It's an issue of modeling the dimension of time in your database schema. Typically, the current status is what you want to check when you query the tables: how much stuff is in stock, how many projects are pending, etc. all come with the implicit requirement of "at this time." But other reports would question "how did this inventory vary over time" which would trace history/audit-trail records.
This is probably the best way for me to think about this.
It may or may not be needless to say that it's waaaay too late to change the design of the database and related code in such a fundamental way--if I concluded that, in my specific case, it would be the best way to go. I've given you one example of a problem that occurs in several different modules of the system, each with its own set of parent records and child tables, many of which have "expiration" or "supercession" (is that a word?) dates. This isn't sales with invoices and orders; it's a quasi-medical case management/service/process tracking and documentation system. Users need to look at the "historical" information frequently.
I did actually write a generic method for my reporting system a few years ago that can accept a few parms and take the long way around (multiple queries and sorting tricks) to produce these kinds of results regardless of the tables being processed. It works for a reasonably large subset of these problems, but this particular case, a report asking for a couple of totals rather than a list of names and related items, is sufficiently different and unusual in my case to make me think about doing something different. Unfortunately, I can't do something THAT different.
I thought that was a requirement for normalization. It would seem to engender less upkeep than creating a need to update something in the parent record whenever you change something in the child record.
Only if the two things mean the same thing. The Consumer record stores the current state. The Status Audit table stores the history of state changes. They may have similar names, but are in fact different pieces of information.
Consumer: Approved is the current status of the process Status table: Approved, {^2018-6-21) is the date the process got that status.
I think I have to respectfully disagree here. Perhaps it's only the (undiagnosed) Aspergerishness in me. But I am an entirely self-taught developer who does not really understand set theory very well. A program that I produced in the early 1990s to perform some of the tasks the current system handles had a single, very large table that stored everything. At the time it was required to report whether a specific service had been delivered to a specific consumer at least once per quarter. So the table had one row per consumer and a field for each service and each quarter: S1Q1, S2Q1... S1Q3... etc.
There was at that time no need to maintain a historical record of services; at the end of each reporting year the service fields were purged and we started again. Neverthess, I think everyone would say this was a terrible design.
I don't think that adding a different effective date to a piece of data that is otherwise identical to other pieces of data makes it a "different thing" in normalization terms. I can see that in some cases it would be more convenient to do so. But if instead of two or three dated items per parent, what if I had fifteen or twenty?
My system provides a utility function to superusers; they can add new Status options without requiring me to do any programming. And they frequently do this. Following your suggestion, I would have to do programming to modify the parent tables every time they did that. (I can't automate it; I have to boot everybody out of the system to modify tables that are constantly in use.)
You might say, "Oh. That's new information." However, If I had given you 3000 words explaining the full complexity of the system in advance, you would have responded "tl;dr".
I am always struggling to understand the principles behind these things, but every time I think I've found one somebody comes along and tells me, no, that's not it. It makes everything, in the end, seem terribly ad hoc, and that makes it really difficult for me to apply what I've learned to new situations.
Still, I deeply appreciate the time you've given to me and the opportunity to learn something that you always provide.
If changes happen, the data schema might have to change. That's why the database manufacturers gave us that capability.
And this can be severely screwed up, causing major damage. While wearing one of my other hats on this job, for the last three weeks I have been struggling with a company that provides Medicaid billing software. A user had a question. The company refused to answer the question until we installed the latest version. We always approach this with trepidation, because there are always problems. But I installed the update. The process went without a hitch and all of the tests on the data afterwards checked out.
However, as the users began working with the system, they began encountering major slowdowns, and if more than one was in the system at the same time, it would crash with "deadlock" errors.
Company support suggested that we had hit the wall on the SQL Server Express we were using and suggested we purchase the full version of SQL Server. So I spent $1300 to do that, and installed it. Even though the amount of data that was being processed before the update--at least in terms of what we had entered--had not changed after the update.
The updating process required, as it always does, a lengthy "conversion" of the database (it took nearly an hour on a Windows 2012 server VM with 24 GB of RAM and 4 very beefy Xeon processors). There's a detailed list of the changes made in each version. They added a bunch of new fields and tables, among other things, and probably moved things around in the manner that you suggest is necessary and routine.
As a result, the size of the database was significantly bloated--and if I had to guess, they did not thoroughly test the updates with datasets the size of ours (which is, apparently, unusually large for them), to ensure that everything was optimized. The conversion process may even have corrupted the data. Since then we have been arguing with them daily, and they keep trying to tell me that my hardware capacity (which exceeds their minimum specifications by a huge margin), or the out-of-the-box SQL Server configuration (which I do not know how to change and for which I would have to hire a consultant), had suddenly become too weak for the system between the time they took a version of the database that was working fine, and converted it--without us adding any data--to a much larger database that does not work fine. We are now facing a serious cash flow problem because we can't get enough billing out on a daily basis.
I need a total of the Hours stored to all of the LAST hours
records that are
owned by Consumers whose LAST status record contains "Approved" in the "type" field.
Bear in mind that a Consumer may not own any records in the Hours table at all.
New info.
Welcome to my world. The "customer" for this particular module is extremely inarticulate. I always go through multiple iterations with her before I finally get to giving her what she really wants. I've been working with her for a long time; she is not getting any better at telling me at the beginning exactly what I should do. Here is the request from her that initiated this process, verbatim:
"Can you create a report so I can determine how many people are currently approved for services and the total amount of units the people are approved for?"
Having learned through experience that talking to her about it would not produce significantly greater clarity, I just went ahead and started developing some code that would appear to answer her question. I really wanted to just run an ad hoc query in the development environment without constructing an interface or integrating it into the system so I could show her what answer my interpretation of the question would provide. I knew she would then say, "No.. it's not really that..." and we would be off to the races.
As for the code: I will test yours and Frank's solutions and respond later today.
Thank you very much!!
Ken
-
Ken Dibble