On Thu, Jun 21, 2018 at 2:22 PM, Ken Dibble <[email protected]> wrote:
>
> 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.
> 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.
> Also, if a need
> develops for the parent to own more than one of something else that was not
> part of the original design, something which would require yet another child
> table,
That's a "yeah, but what if" hypothetical. By that rationale, all data
schemas are out of date, and we should just use document databases.
Let's skip that slippery slope.
If changes happen, the data schema might have to change. That's why
the database manufacturers gave us that capability.
> I would then also have to modify the parent table to add a "most
> recent" field for that. Modifying existing tables containing data seems more
> risky than simply adding empty tables--especially when I have to do it in a
> completely automated way for remote locations whose hardware I don't trust.
Solving hardware problems with bad software is rarely a win. Backup,
migrate, validate, rollback or commit.
>> > The results have to total the most recent hours records belonging to
>> > Consumers whose most recent Status is "Approved".
>>
>> I think your description of the hours result is a little ambiguous,
>> and since I've guessed wrong twice, I'll stop now. ;)
>>
>> Are you SUM'ming Hours records for the Consumer SINCE their status
>> became approved, or are you picking the LAST Hours record for
>> Consumers currently Approved? Either is easy. Ish.
>
>
> 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.
SELECT Consumer.*, Status.*, Hours.AppHours
FROM Consumer
JOIN Status on Consumer.ConPK=Status.ConFK
LEFT OUTER JOIN Hours on Consumer.ConPK=Hours.ConFK
WHERE Status.Date =
(SELECT MAX(Stat2.Date) FROM Status Stat2 WHERE
Stat2.ConFK = Consumer.ConPK GROUP BY Stat2.ConFK)
AND Status.Type = "Approved"
AND Hours.Date = (SELECT MAX(Hours2.Date) FROM Hours Hours2 WHERE
Hours2.ConFK = Consumer.ConPK GROUP BY Hours.PK)
Where Hours.AppHours come out as NULL, you'll want to substitute zero
to get Approved Consumers who have no Hours record posted.
--
Ted Roche
Ted Roche & Associates, LLC
http://www.tedroche.com
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/cacw6n4tmw40vu7sj5dspadntf82bqedq8r-bj25zbrrdt9p...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
