Re: Record (entity) versions...

"Gene Wirchenko" <genew_at_mail.ocis.net> wrote in message news:e5tue0phd2auv5gostdfmaa62b99ee2foh_at_4ax.com...
> sztomi_at_seznam.cz (sz Tomi) wrote:
>
> [snip]
>
> >SELECT i.date,c.name,c.address FROM invoices AS i INNER JOIN customers
> >AS c ON i.CustomerId=c.CustomerId
> >GO
> >
> >date name address
> >
> >2004-01-01 00:00:00.000 First Company Ltd. Boston
> >2004-01-02 00:00:00.000 First Company Ltd. Boston
> >2004-01-02 00:00:00.000 Second Company Ltd. Washington
> >2004-01-03 00:00:00.000 Second Company Ltd. Boston
> >
> >Is it possible in any of the RDBMS's to make this last query return
> >the following result set?
> >
> >2004-01-01 00:00:00.000 First Company Ltd. New York
> >2004-01-02 00:00:00.000 First Company Ltd. New York
> >2004-01-02 00:00:00.000 Second Company Ltd. Washington
> >2004-01-03 00:00:00.000 First Company Ltd. Boston
>
> No, because you do not have enough information. You need to
> associate a date range with the addresses and use that date range with
> the customer id to select the then-current address. To my somewhat
> less than expert eyes, this would involve creating a table of
> addresses keyed to customer id and effective date.
>
> To those more expert, am I on the right track?
I'm by no means an expert, but if a temporal solution seems too complex and the invoices are meant to reflect a snapshot in time transaction (versus a "current" information in the customers table), you could always add the name of the customer and the city attributes to the invoice (call it invoice address). Since each invoice tuple value should be time invariant, the values will be entirely dependent on the invoice key (all those opposed, yell loudly).

Gene's suggestion is more elegant and more in line with my own personal preferences, but its more complex and there might not be a requirement to have a history of customers and how they change. The requirement might be just to show the customer location on an invoice that was sent.

BTW, the last time I tried to define an "interval" type with Date's operations and constraints on a user-defined type in Oracle, a little over a year ago, I found I couldn't include it as a key. I had to write the representations of DB and table constraints as stored procedures. I have to try the same with the propietary interval data types introduced in 9i and later. Do we have any vendor implementations available that support intervals as part the key now? If so, do they pass muster in terms of the specification given by Date in his _Intro_, 7th Ed.?

Thanks,

• Dan
  >
  > Sincerely,
  >
  > Gene Wirchenko
  >
  > Computerese Irregular Verb Conjugation:
  > I have preferences.
  > You have biases.
  > He/She has prejudices.