MySQL reporting question

[der.hans]

Am 22. Jun, 2010 schwätzte Joseph Sinclair so:

> As I understand your structure and requirements (it's a bit fuzzy, especially the t2.varchar2 == t2.varchar2)
> Create two *materialized* views and refresh both views (1 then 2) every reporting period (daily?).

When a batch is run or when the CEO asks :). Essentially he just needs to
know statuses from our batches, so the former might be sufficient.

>  View1 : select status, varchar1, varchar2, max(timestamp) from t2 where varchar1 != NULL AND max(timestamp) GROUP BY status, varchar1, varchar2;

I think I would first need a view that gets varchar2 and status where
max(timestamp), group by varchar2. varchar1 can be NULL in t2.

We've solved much of the problem by deciding to add columns to t1. I think
the program that builds t1 can also get varchar2. Even if it can't, I
can add varchar2 from my logs that have varchar1 in them. It'll mean t1
doesn't get some updates, but business analytics group doesn't care about
any of those updates anyway.  All the updates they care about are from
after varchar1 shows up in my logs.

I will then do update t1 adding each new status based on varchar2.
Theoretically my logs should never get an intermediate status after a
final status, so last one in will be the winner.

With varchar2 in both tables, it will be much easier to do sanity checks
for monitoring to make sure data is staying in sync and the last status in
is truly a terminal status.

Rather than doing the t1 update, would it be more efficient to use a
trigger for the updates?

ciao,

der.hans
-- 
#  http://www.LuftHans.com/Classes        http://www.TwoGeekTechs.com/
#  Director of Engineering, FonWallet Transaction Solutions, Inc.
#  "We should not be building surveillance technology into standards.
#  Law enforcement was not supposed to be easy.
#  Where it is easy, it's called a police state."  -- Jeff Schiller