Oninit cdi / Archive
Talk to an expert →

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

Posted in 1998

Topics: SQL Development & Query Writing, Triggers, Constraints & Referential Integrity

Vipul M. Shah — — source: Informix-list mailing list archive (1991-1998)

This is a multi-part message in MIME format. ------=_NextPart_000_000F_01BDAB26.550D16B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Thanks. This works great. 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. 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. Issues: One, all those tables end up sharing and ID space. Two, how about table contention? 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? Any ideas? -----Original Message----- From: Jonathan Leffler [mailto:jleffler@informix.com] Sent: Wednesday, July 08, 1998 16:27 To: Vipul M. Shah Cc: informix-list@iiug.org Subject: Re: Common (I think) gripe and question about Relational Databases 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. > or should I chalk this up for the "I hate relational databases" > department? You could do that, but it seems a little OTT to do so. > If there is, how do I use this way in the modeling tool I use > (ERwin/ERX)? Probably the same basic way you handled your original design. Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix -- see http://www.perl.com/CPAN ------=_NextPart_000_000F_01BDAB26.550D16B0 Content-Type: image/jpeg; name="ATT00044.jpeg" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="ATT00044.jpeg" BEGIN:VCARD VERSION:2.1 N:Shah;Vipul;M. FN:Vipul M. Shah ORG:Healtheon Corp. TITLE:MTS TEL;WORK;VOICE:(408) 876-5067 TEL;HOME;VOICE:(408) 615-7370 TEL;WORK;FAX:(408) 876-5256 ADR;WORK:;;4600 Patrick Henry Drive;Santa Clara;CA;95054-1817;US LABEL;WORK;ENCODING=3DQUOTED-PRINTABLE:4600 Patrick Henry = Drive=3D0D=3D0ASanta Clara, CA 95054-1817=3D0D=3D0AUS ADR;HOME;ENCODING=3DQUOTED-PRINTABLE:;;3131 Homestead Rd=3D0D=3D0AApt = 5F;Santa Clara;CA;95051-5416 LABEL;HOME;ENCODING=3DQUOTED-PRINTABLE:3131 Homestead Rd=3D0D=3D0AApt = 5F=3D0D=3D0ASanta Clara, CA 95051-5416 URL: URL:http://www.bigfoot.com/~vipul.m.shah EMAIL;PREF;INTERNET:vipul@healtheon.com EMAIL;INTERNET:vipul.m.shah@bigfoot.com REV:19980709T174250Z END:VCARD ------=_NextPart_000_000F_01BDAB26.550D16B0 Content-Type: application/octet-stream; name="Vipul M. Shah.vcf" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="Vipul M. Shah.vcf" BEGIN:VCARD VERSION:2.1 N:Shah;Vipul;M. FN:Vipul M. Shah ORG:Healtheon Corp. TITLE:MTS TEL;WORK;VOICE:(408) 876-5067 TEL;HOME;VOICE:(408) 615-7370 TEL;WORK;FAX:(408) 876-5256 ADR;WORK:;;4600 Patrick Henry Drive;Santa Clara;CA;95054-1817;US LABEL;WORK;ENCODING=3DQUOTED-PRINTABLE:4600 Patrick Henry = Drive=3D0D=3D0ASanta Clara, CA 95054-1817=3D0D=3D0AUS ADR;HOME;ENCODING=3DQUOTED-PRINTABLE:;;3131 Homestead Rd=3D0D=3D0AApt = 5F;Santa Clara;CA;95051-5416 LABEL;HOME;ENCODING=3DQUOTED-PRINTABLE:3131 Homestead Rd=3D0D=3D0AApt = 5F=3D0D=3D0ASanta Clara, CA 95051-5416 URL: URL:http://www.bigfoot.com/~vipul.m.shah EMAIL;PREF;INTERNET:vipul@healtheon.com EMAIL;INTERNET:vipul.m.shah@bigfoot.com REV:19980709T174250Z END:VCARD ------=_NextPart_000_000F_01BDAB26.550D16B0--

Related threads