|
On 08/10/14 13:29, Jim Nasby wrote:
On
10/6/14, 6:10 PM, Gavin Flower wrote:
Even if timestamps are used extensively,
you'd have to be careful joining on them. You may have
information valid at T1 and changing at T3, but the transaction
has T2, where T1 < T2 < T3 - the appropriate set of data
would be associated with T1, would would not get anywhere trying
to find data with a timestamp of T2 (unless you were very
lucky!).
Yeah, this is why I think timestamps need to be shunned in favor
of explicit pointers. Anyone that thinks timestamps are good
enough hasn't thought the problem through completely. :)
I also think there's potential value to storing full transaction
information (presumably in a separate table): txid_current(),
txid_current_snapshot(), now(), current_user, maybe some other
stuff (client IP address?). That way you can tell exactly what
created a history record. With appropriate shenanigans you can
theoretically determine exactly what other history data would be
visible at that time without using pointers (but man would that bu
ugly!)
Actually things like phone numbers are
tricky. Sometimes you may want to use the current phone number,
and not the one extant at that time (as you want to phone the
contact now), or you may still want the old phone number (was
the call to a specific number at date/time legitimate & who
do we charge the cost of the call too).
Yeah, I'm pretty convinced at this point that history/versioning
should be built on top of a schema that always contains the
current information, if for no other reason than so you always
have a PK that points to what's current in addition to your
history PKs.
One of the motivations for having an effective_date, was being able
to put changes into the database ahead of time.
Finding the current value uses the same logic a find the value at
any other date/time - so you don't need a special schema to
distinguish the current state from anything else. For example:
DROP TABLE
IF EXISTS stock;
CREATE TABLE stock
(
id
text,
effective_date
timestamptz,
price
numeric
);
INSERT INTO stock
(
id,
effective_date,
price
)
VALUES
('y88',
'2014-10-01', 12.0),
('x42',
'2014-10-01', 12.1),
('x42',
'2014-10-08', 12.2),
('x42',
'2014-10-10', 12.3),
('x42',
'2014-10-16', 12.4),
('z42',
'2014-10-19', 12.5),
('z49',
'2014-10-01', 12.6),
('z49',
'2014-10-30', 12.7),
('z77',
'2014-10-01', 12.8);
CREATE UNIQUE INDEX
primary_key ON stock (id ASC, effective_date DESC);
SELECT
s.price
FROM
stock s
WHERE
s.id = 'x42'
AND
s.effective_date <= '2014-10-11'
ORDER BY
s.effective_date
DESC
LIMIT 1;
Cheers,
Gavin
|
