Re: Common (I think) gripe and question about Relational Databases
This is a multi-part message in MIME format.
------=_NextPart_000_0023_01BDAB26.51C31BD0
Content-Type: multipart/related;
boundary="----=_NextPart_001_0024_01BDAB26.51C31BD0";
type="multipart/alternative"
------=_NextPart_001_0024_01BDAB26.51C31BD0
Content-Type: multipart/alternative;
boundary="----=_NextPart_002_0025_01BDAB26.51C31BD0"
------=_NextPart_002_0025_01BDAB26.51C31BD0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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
> ( =20
> 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 =3D 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 =3D D2.Id
AND D1.Effective_Date <=3D D2.Termination_Date
AND D1.Termination_Date >=3D =
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_002_0025_01BDAB26.51C31BD0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 HTML//EN">
<HTML>
<HEAD>
<META content=3Dtext/html;charset=3Diso-8859-1 =
http-equiv=3DContent-Type><!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 =
HTML//EN"><BASE=20
href=3D"file://C:\\Program Files\\Common Files\\Microsoft =
Shared\\Stationery\\">
<META content=3D'"MSHTML 4.72.3110.7"' name=3DGENERATOR>
<STYLE>
<!--
body
{
font-family: "MS Sans Serif", "sans serif";
font-weight: plain;
font-size: 12pt;
color: "#008080";
margin-left: 50;
background-repeat: repeat-y;
}
HR
{
color: "#008080";
width: "100%";
height: 1;
}
-->
</STYLE>
</HEAD>
<BODY =
background=3Dcid:001201bdab60$fe174570$4332040a@hcwvshah.healtheon.com=20
bgColor=3D#ffffff>
<DIV>
<P><FONT color=3D#0000ff>Thanks. This works great. The only =
issue I=20
have with this is that this would get really painful really fast if I =
have too=20
many tables with this feature. In almost all industries I have =
worked in,=20
Securities Trading, Healthcare,... a vast majority of the information is =
effective-dated. If I have to add one table for each table that =
has=20
effective dates, I would add 75% more tables.</FONT></P>
<P><FONT color=3D#0000ff>The other choice I have is to have a table =
enumeration=20
field in the DatumKey table, that tells me which effective_date table I =
am going=20
against, and then have a central table for all IDs.</FONT></P>
<P><FON