Re: Referencing a view?

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