Thread: Referencing a view?

On Thu, 12 Jul 2001, James Orr wrote:

> Hi,
> 
> Is there anyway that you can reference a column in a view for
> referential integrity?  The problem is with the unique thing,
> obviously I can't create a unique index on a view.  Here is what I
> have:

Not right now, and actually you still wouldn't get something that would
work even with a unique index.  We'd have to be able to push the
constraint conditions down into the two tables that you're unioning to
make it work correctly (think about the triggers on the referenced table
for update and delete - which in your case with a unique index would
be safe to put the triggers on all the time in both, but some view
conditions would be such that that wouldn't be sufficient).

You *might* be able to add the triggers manually and have it work.  I
haven't tried, and wouldn't guarantee it at all...

Jul 12, 16:25 -0400, James Orr wrote:

Much better will be change database structure - you have absolutely
identical tables, so is not good from normalization point of view.
Better use one table for address, and link it to one table for
"entity" - both person and company, and "entity" link to specific
information about person and company.
Somathing like this:

create table entity(
id_entity serial not null primary key,
_all_other_fields_,
.........);

create table address(
id_address serial not null primary key,
id_entity integer not null references entity,
_all_other_fields_,
.........);

create table person(
id_entity integer not null primary key references entity,
first_name text,
last_name text,
...........);

create table company(
id_entity integer not null primary key references entity,
company_name text,
........);

It may be usefull to add column 'who_is' boolean in the 'entity' table -
when you will do a search on 'address' table you will get 'id_entity',
and you can do a search on 'entity' table and get 'who_is', and than you
can get other information fom 'person' or 'company' tables.

> Hi,
>
> Is there anyway that you can reference a column in a view for referential integrity?  The problem is with the unique
thing,obviously I can't create a unique index on a view.  Here is what I have:
 
>
> CREATE SEQUENCE "addresses_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1  cache 1 ;
>
> CREATE TABLE "org_addresses" (
>  "id" integer DEFAULT nextval('addresses_id_seq'::text) NOT NULL,
>  "orgid" integer references orgs on delete cascade,
>  "name" character varying(255),
>  "street1" character varying(255),
>  "street2" character varying(100),
>  "city" character varying(100),
>  "state" character(2),
>  "zip" character(10),
>  Constraint "org_addresses_pkey" Primary Key ("id")
> );
>
> CREATE TABLE "user_addresses" (
>  "id" integer DEFAULT nextval('addresses_id_seq'::text) NOT NULL,
>  "userid" integer references users on delete cascade,
>  "name" character varying(255),
>  "street1" character varying(255),
>  "street2" character varying(100),
>  "city" character varying(100),
>  "state" character(2),
>  "zip" character(10),
>  Constraint "user_addresses_pkey" Primary Key ("id")
> );
>
> CREATE VIEW "addresses" as SELECT user_addresses.id, user_addresses.userid, user_addresses.name,
user_addresses.street1,user_addresses.street2, user_addresses.city, user_addresses.state, user_addresses.zip FROM
user_addressesUNION SELECT org_addresses.id, NULL::unknown, org_addresses.name, org_addresses.street1,
org_addresses.street2,org_addresses.city, org_addresses.state, org_addresses.zip FROM org_addresses;
 
>
> So this gives me a view with every address, each with a unique id as I used the same sequence in both tables.  Now
whatI want to do is something like this :
 
>
> CREATE TABLE orders (
>     id serial primary key,
>     shipping_address int references addresses(id),
> .
> .
> );
>
> Which of course doesn't work because addresses as a view can't have a unique index.  Any way around this?
>
> - James
>

my best regards,
----------------
Grigoriy G. Vovk

Thanks for all the responses!  The one from Grigoriy was particularly
interesting, I hadn't thought of that approach.

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.

Thanks!
- James

Jul 13, 09:41 -0400, James Orr wrote:

> Thanks for all the responses!  The one from Grigoriy was particularly
> interesting, I hadn't thought of that approach.
>
> 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.
Yes, its really a problem... When  information sometime changes but we
want to keep old information for depending data. (If I understand correctly
what about you are speaking).
But, you can resolve the problem if will copy information about address
for executed order in a different place as a text, not as a link to the
table 'address'. So, after execution of an order you will take current
address by the link and copy all information (may be, as one field
everything - concat all fields from the address together). Its a one
solution.
Next - you can use many addresses for an entity, and work only with some
of them, 'valid', or 'current', as do you like, not with all.
So, you will have full addresses history - when was changed, etc. And the
order will always links to correct address, even now current address is
different.
You can do it by add field 'current' bool in the table 'address' or
create additional table 'current_address' which will have 'id_entity' and
'id_address', so if you will do a search on 'current_address' you got
a list of current addresses, in case you will do a search on 'address' -
you got a list of all addresses. I prefere to create additional table.
May be I've explained not well - ask, I'll type an database structure
example, for developers it may be easy way :)


my best regards,
----------------
Grigoriy G. Vovk

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