Re: Referencing a view?

James,

> However, I came to the realization that if somebody changes their
> address, I
> don't want it to be changed on previous orders.  So I think i'll
> change the
> orders table to contain the actual address information and use an
> INSERT ...
> SELECT instead.  That way I can be sure I have an accurate record of
> all
> orders.

Hey, if you really want to be sophisticated about it, you should have
multiple addresses per customer, with perhaps a status selector to
indicate which addresses are new and which are old.  For my current
Postgres app, it goes futher than that to support companies with many
contacts and locations:



Company              Address              Contact
. CompanyID -----------CompanyID------------CompanyID
. Name                 AddressID------------AddressID
.                      Status/Type          Status/Type
.                      Address              Name, Title


This allows me to have mutliple addresses per company (including old
addresses for historical compatibility) and multiple contacts at each
address.  There's also an elegant (at least I think so) way to maintain
a "default" contact and address for each company.

Of course, this all requires some sophistication in the user interface,
as one has to make the user distinguish between address corrections
(UPDATE) and new addresses (INSERT) which possibly replace old addresses
(UPDATE SET STATUS = old).

-Josh Berkus



______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco