> > 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
