Re: Common (I think) gripe and question about Relational Databases

On Thu, 9 Jul 1998, Vipul M. Shah wrote: > Thanks. This works great. Good. > The only issue I have with this is that this would get really painful > really fast if I have too many tables with this feature. In almost all > industries I have worked in, Securities Trading, Healthcare,... a vast > majority of the information is effective-dated. If I have to add one > table for each table that has effective dates, I would add 75% more > tables. It isn't as good as a full-blown temporal database (or a time-travel facility as in Illustra). > The other choice I have is to have a table enumeration field in the > DatumKey table, that tells me which effective_date table I am going > against, and then have a central table for all IDs. That is probably harder to manage, and probably slower. It could be made to work. There might be contention problems too, though the table would be primarily used for reading rather than update. > Issues: One, all those tables end up sharing and ID space. If the keys are meaningless (which they are), then it doesn't matter if ID 10000 belongs to one table and ID 10001 belongs to a different table. > Two, how about table contention? I'd assume that new Data (DatumKeys) are inserted fairly often, old ones are seldom deleted, and that most access to the DatumKey table would be read-only. This leads to low contention, even with high isolation levels. > Three, if I have a lot of tables joined to one, and have an ON DELETE > CASCADE clause on each relationship, wouldn't that slow down deletes to a > crawl? Yes. Note that the ON DELETE CASCADE clause would only apply between the DatumKey table and the Datum table. The referencing tables would only apply a ON DELETE RESTRICT clause, which would prevent the row in the DatumKey table being deleted while there was still a reference to the DatumKey value. But, in general, you are not often going to be deleting DatumKey values, so the performance should not be a big problem -- even if you have a single DatumKey table providing the reference numbers for lots of other tables. You're raising valid questions. The DatumKey tables can be quite small because they are all-key, but that doesn't make them vanish. However, volume of data cannot be a big issue as you have all those Datum tables occupying a lot more space than the simple 'current-value-only' schemes where the referring tables would store the data value directly in the referring table (or, at one level of indirection, the referring tables would store the Datum.ID value as now, but the Datum table would only contain the ID and the data value). And a temporal database would probably use a similar mechanism to this, though the types of the effective date and termination date would probably be different. Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix -- see http://www.perl.com/CPAN > From: Jonathan Leffler [mailto:jleffler@informix.com] > Sent: Wednesday, July 08, 1998 16:27 > > On Wed, 8 Jul 1998, Vipul M. Shah wrote: > > I have a data value (anything) that changes over time. It has a unique > > identifier that doesn't. Things refer to it by that unique ID. I am > > guaranteed that at any given instant in time, I have exactly one datum > > for that ID. So, > > > > -- Informix-ese > > create table datum > > ( > > id serial(1) not null, > > effective_date date not null, > > termination_date date not null, > > data_value integer, > > primary key (id, effective_date) > > )> > > > create table referrer > > ( > > -- something, something > > id integer references datum (id) > > )> > > > Obviously, this is not legal, either in ANSI SQL or in Informix SQL. > > No, the referenced column must have a UNIQUE or PRIMARY KEY constraint > on it (or, I guess, a unique index, but the 7.2 manual says one of those > two contraint types). > > > What I am trying to do is to refer to datum by it's ID, which is not > > unique over time, but is unique at any given instant in time. > > OK. This is the time travel feature in the Illustra database, amongst > others. > > > I can, of course, eschew declarative referential integrity altogether, > > and handle it using triggers. This would, however, become very painful > > very quickly, especially as the number of referring entities increased. > > Yes. > > > Is there any good way to handle this, > > Yes, adapting one of Codd's RM/T ideas... > > CREATE TABLE DatumKey > ( > Id SERIAL NOT NULL PRIMARY KEY PK_DatumKey > {...description of what this value represents?...} > );> > CREATE TABLE Datum > ( > Id INTEGER NOT NULL REFERENCES DatumKey > {ON DELETE CASCADE} CONSTRAINT FK_Datum, > Effective_Date DATE NOT NULL, > Termination_Date DATE NOT NULL, > Data_Value INTEGER, > PRIMARY KEY (Id, Effective_Date) CONSTRAINT PK_Datum > );> > CREATE TABLE Referer > ( > -- something, something > Id INTEGER REFERENCES DatumKey CONSTRAINT FK_Referer > )> > The DatumKey table becomes the entity definition table; the referring tables > are always dependent on whether the row exists in the DatumKey table. > However, when you want the value that applied at some reference time, then > you join with Datum rather than DatumKey: > > SELECT R.*, D.Data_Value > FROM Referer R, Datum D > WHERE R.Id = D.Id > AND reference_date BETWEEN D.Effective_Date AND D.Termination_Date; > > This is what you would have written previously; the difference is that the > two tables both have a foreign key which refers to the same third table, > rather than either of them referring to the other. > > There is also a complex constraint on the rows in the Datum table: > > RANGE OF D1 IS Datum; > RANGE OF D2 IS Datum; > > FOREACH D1 NOT EXISTS D2 (D1.Id = D2.Id > AND D1.Effective_Date <= D2.Termination_Date > AND D1.Termination_Date >= D2.Effective_Date) > > You can translate that into SQL but it gets rather hairy! You'd need to > double check the semantics for the end dates; this condition assumes that > you cannot have two rows where the effective date of one is the same as the > termination date of the other. > [...]