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

Vipul M. Shah wrote: > > 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)? It seems that the relationship is many datum for each referrer. I think what you want to do is either reverse the relationship so that the referrer table is parent or if that is not logically sensible then create a false master/parent table so the relationship is either create table referrer ( --somestuff id serial(1) primary key ); create table datum ( id integer not null, effective_date date not null, -- stuff primary key (id, effective_date), foreign key (id) references referrer (id) ); -OR- create table identity ( id serial(1) not null primary key, -- optional descriptive data ); create table datum ( id integer not null, effective_date date not null, -- stuff primary key (id, effective_date), foreign key (id) references identity (id) ); create table referrer ( --somestuff id integer primary key, foreign key (id) references identity (id) ); It would appear that the relationship between referrer and the new master table, identity, is 1-to-1. Third normal form would require that they be folded into a single table and certainly if there is no other information contained in identity than the primary key, id, then the first schema is correct TNF and the latter schema is pathological. Art S. Kagel