quarterly changes

[Carter Harvey]

I currently have a table of contracts with their
parameters
(include/exclude,effectivedate,termdate,delegated, etc)
Some of this info is static and will never change. Some of
this data can change each quarter. At present, all this
data is static, which is creating reporting problems when
something changes mid-year.
I'd like to be able to break this data out to several
tables, linked by contractID's, so that only the changes
are stored, rather than storing the same info for each
contract for all quarters (1500 contracts *4quarters
*several tables = lots of redundant data..)
I've created a query that takes a simple table with static
info (i.e. contract name), and compares it to a table with
qrtrs (just 1 field with values 1 thru 4) filtered for the
current quarter (<=qrtr), then use that query to compare
against a table that stores changes to variable contract
info (i.e. effective/terminated dates) However, I wind up
with a duplicate record for quarters with changes (one
with the old data, one with the new data).. I can't join
the query and the table by qrtr, as that would only show
data from the change table, excluding qrtrs without
changes (i.e. 1q first entry, 2q no change/entry, 3q
change entry.. joining on qrtr gives 1q & 3q only, but not
joining on qrtr gives 1q-1qdata, 2q-1qdata, 3q-1qdata, 3q-
3qdata) Are there examples of queries/db's for tracking
changes across time that could help with this?

Thanks in advance,
Carter

 

[Allen Browne]

The idea of storing only what changed and assuming the other data sounds
scary to me.

IMHO, you do need a record for each entry. If you work and charge in
quarters, then you need a quarterly entry of each client. If the contract is
for 2 years, you could have a single entry for 2 years if you don't mind the
issues that are associated with non-granular timeframes (specifying start
date, end date, and correctly interpreting the in-between's).

We can't see your data structure, of course, but there should not be "lots
of redundant data" in a correctly normalized structure. Lots of repetitive
data, yes, but not redundant. To save data entry, you could have mechanisms
to auto-generate the data (e.g. templates, or a
duplicate-last-quarter's-contracts button). From what you have described,
though, it seems important to know that you did have a contract for the
client for each period.