Common (I think) gripe and question about Relational Databases
This is a multi-part message in MIME format.
------=_NextPart_000_0019_01BDAA5C.78DCEEA0
Content-Type: multipart/alternative;
boundary="----=_NextPart_001_001A_01BDAA5C.78DCEEA0"
------=_NextPart_001_001A_01BDAA5C.78DCEEA0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi all,
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. =
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.
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.
Is there any good way to handle this, or should I chalk this up for the =
"I hate relational databases" department? If there is, how do I use =
this way in the modeling tool I use (ERwin/ERX)?
Thanks. Please CC responses to vipul@healtheon.com.
-------------------------------------------------------------------------=
-------
Vipul
------=_NextPart_001_001A_01BDAA5C.78DCEEA0
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><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:001001bdaa97$2533fe70$4332040a@hcwvshah.healtheon.com=20
bgColor=3D#ffffff>
<P>Hi all,</P>
<P>I have a data value (anything) that changes over time. It has a =
unique=20
identifier that doesn't. Things refer to it by that unique =
ID. I am=20
guaranteed that at any given instant in time, I have exactly one datum =
for that=20
ID. So,</P>
<P>-- Informix-ese<BR>create table datum<BR>(<BR> id =
serial(1)=20
not null,<BR> effective_date date not=20
null,<BR> termination_date date not=20
null,<BR> data_value integer,<BR> =
primary=20
key (id, effective_date)<BR>)</P>
<P>create table referrer<BR>(<BR> -- something,=20
something<BR> id integer references datum =
(id)<BR>)</P>
<P>Obviously, this is not legal, either in ANSI SQL or in Informix =
SQL. =20
What I am trying to do is to refer to datum by it's ID, which is not =
unique over=20
time, but is unique at any given instant in time.</P>
<P>I can, of course, eschew declarative referential integrity =
altogether, and=20
handle it using triggers. This would, however, become very painful =
very=20
quickly, especially as the number of referring entities increased.</P>
<P>Is there any good way to handle this, or should I chalk this up for =
the=20
"I hate relational databases" department? If there is, =
how do I=20
use this way in the modeling tool I use (ERwin/ERX)?</P>
<P>Thanks. Please CC responses to <A=20
href=3D"mailto:vipul@healtheon.com">vipul@healtheon.com</A>.</P>
<DIV>
<HR>
</DIV>
<DIV><FONT style=3D"BACKGROUND-COLOR: #ffffff">Vipul</FONT></DIV>
<P> </P></BODY></HTML>
------=_NextPart_001_001A_01BDAA5C.78DCEEA0--
------=_NextPart_000_0019_01BDAA5C.78DCEEA0
Content-Type: image/jpeg
Content-Transfer-Encoding: base64
Content-ID: <001001bdaa97$2533fe70$4332040a@hcwvshah.healtheon.com>
/9j/4AAQSkZJRgABAgEASABIAAD/7QSyUGhvdG9zaG9wIDMuMAA4QklNA+kAAAAAAHgAAwAAAEgA
SAAAAAADBgJS//f/9wMPAlsDRwUoA/wAAgAAAEgASAAAAAAC2AIoAAEAAABkAAAAAQADAwMAAAAB
Jw8AAQABAAAAAAAAAAAAAAAAYAgAGQGQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA4
QklNA+0AAAAAABAASAAAAAEAAQBIAAAAAQABOEJJTQPzAAAAAAAIAAAAAAAAAAA4QklNBAoAAAAA
AAEAADhCSU0nEAAAAAAACgABAAAAAAAAAAI4QklNA/UAAAAAAEgAL2ZmAAEAbGZmAAYAAAAAAAEA
L2ZmAAEAoZmaAAYAAAAAAAEAMgAAAAEAWgAAAAYAAAAAAAEANQAAAAEALQAAAAYAAAAAAAE4QklN
A/gAAAAAAHAAAP////////////////////////////8D6AAAAAD/////////////////////////
////A+gAAAAA/////////////////////////////wPoAAAAAP//////////////////////////
//8D6AAAOEJJTQQAAAAAAAACAAA4QklNBAIAAAAAAAIAADhCSU0ECAAAAAAAEAAAAAEAAAJAAAAC
QAAAAAA4QklNBAkAAAAAAqIAAAABAAAAgAAAAAIAAAGAAAADAAAAAoYAGAAB/9j/4AAQSkZJRgAB
AgEASABIAAD//gAnRmlsZSB3cml0dGVuIGJ5IEFkb2JlIFBob3Rvc2hvcKggNC4wAP/uAA5BZG9i
ZQBkgAAAAAH/2wCEAAwICAgJCAwJCQwRCwoLERUPDAwPFRgTExUTExgRDAwMDAwMEQwMDAwMDAwM
DAwMDAwMDAwMDAwMDAwMDAwMDAwBDQsLDQ4NEA4OEBQODg4UFA4ODg4UEQwMDAwMEREMDAwMDAwR
DAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDP/AABEIAAIAgAMBIgACEQEDEQH/3QAEAAj/xAE/
AAABBQEBAQEBAQAAAAAAAAADAAECBAUGBwgJCgsBAAEFAQEBAQEBAAAAAAAAAAEAAgMEBQYHCAkK
CxAAAQQBAwIEAgUHBggFAwwzAQACEQMEIRIxBUFRYRMicYEyBhSRobFCIyQVUsFiMzRygtFDByWS
U/Dh8WNzNRaisoMmRJNUZEXCo3Q2F9JV4mXys4TD03Xj80YnlKSFtJXE1OT0pbXF1eX1VmZ2hpam
tsbW5vY3R1dnd4eXp7fH1+f3EQACAgECBAQDBAUGBwcGBTUBAAIRAyExEgRBUWFxIhMFMoGRFKGx
QiPBUtHwMyRi4XKCkkNTFWNzNPElBhaisoMHJjXC0kSTVKMXZEVVNnRl4vKzhMPTdePzRpSkhbSV
xNTk9KW1xdXl9VZmdoaWprbG1ub2JzdHV2d3h5ent8f/2gAMAwEAAhEDEQA/APROif0Kv6X81T9L
j+ar/m/5K0F8rJJIfqlJfKySKn6pSXyskkp+qUl8rJJKfqlJfKySSn6pSXyskkp+qUl8rJJKfqlJ
fKySSn//2ThCSU0EBgAAAAAABwABAAAAAQEA//4AJ0ZpbGUgd3JpdHRlbiBieSBBZG9iZSBQaG90
b3Nob3CoIDQuMAD/7gAOQWRvYmUAZIAAAAAB/9sAhAAMCAgNCQ0VDAwVGhQQFBogGxoaGyAiFxcX
FxciEQwMDAwMDBEMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMAQ0NDREOERsRERsUDg4OFBQO
Dg4OFBEMDAwMDBERDAwMDAwMEQwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAz/wAARCAAYBaAD
ASIAAhEBAxEB/90ABABa/8QBPwAAAQUBAQEBAQEAAAAAAAAAAwABAgQFBgcICQoLAQABBQEBAQEB
AQAAAAAAAAABAAIDBAUGBwgJCgsQAAEEAQMCBAIFBwYIBQMMMwEAAhEDBCESMQVBUWETInGBMgYU
kaGxQiMkFVLBYjM0coLRQwclklPw4fFjczUWorKDJkSTVGRFwqN0NhfSVeJl8rOEw9N14/NGJ5Sk
hbSVxNTk9KW1xdXl9VZmdoaWprbG1ub2N0dXZ3eHl6e3x9fn9xEAAgIBAgQEAwQFBgcHBgU1AQAC
EQMhMRIEQVFhcSITBTKBkRShsUIjwVLR8DMkYuFygpJDUxVjczTxJQYWorKDByY1wtJEk1SjF2RF
VTZ0ZeLys4TD03Xj80aUpIW0lcTU5PSltcXV5fVWZnaGlqa2xtbm9ic3R1dnd4eXp7fH/9oADAMB
AAIRAxEAPwCv0T+n4/8AxrP+qavW15J0U/r+P/xrP+qavWg8eKElsWSHZfXWYe4A+ZUMjIFTJBE/
Fc1kXbg63mJP+amk0uesGqdc19Sup2ZrLmWGQxwLR4B35v8A0V0qKlJJJIqUkkkkpSSSSSlJJJJK
UkkkkpSSSSSlJJJJKUkkkkpSSSSSlJJJJKUkkkkpSSSSSlJJJJKUkkkkpSSSSSlJJJJKUkkkkpSS
SSSlJJJJKUkkk