On Thu, Jun 21, 2018 at 2:22 PM, Ken Dibble krdibble@stny.rr.com 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.