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_addresses UNION 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 what I 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