Thread: using tables as types in other tables

In the app we are developing the concept of an address will occur very
often on many tables (vendor, clients, employees, etc.) so we are
looking to avoid code duplication by centralizing the addresses in one
table. However I once read on one of the pgsql- lists that one could use
a table name as a type:

create table address(street text, zip text, city text, country text);

create table employee(emp_addr address, emp_id int);

This is accepted by Postgres but the question is how to insert into the
employee table? What syntax should be used? I tried:

insert into employee values (('mystreet','myzip','mycity','mycountry'), 1);

But it doesn't work.

Should I proceed along that path or would I be better off using a
central address table with keys to the other tables?

Thanks in advance for your insight,


--
    THESEE: D'un perfide ennemi j'ai purg� la nature ;
            A ses monstres lui-m�me a servi de p�ture ;
                                          (Ph�dre, J-B Racine, acte 3, sc�ne 5)

I went over this ground about six months ago, and the bottom line is: until somebody picks this up and sorts out the code (which suffers from bitrot) it's not going to work like you described (believe me, I tried).  Put the addresses in a single table, with foreign keys from each other table that you need.  What you may decide to do is forward- rather than reverse-reference, possibly even with a resolution table between the address table, and the actual entity tables:

client >-----|
             |
             |
vendor >--------< address_mem >-----< address
             |
             |
employee >---|
 
This uses sub-typing, which SQL doesn't deal with very well, but it's an idea.  Of course, if you carry it a step further, and use PGs inheritance, then you can make life a little easier for yourself:

                             /--*client
                            /
                           /
address >-----< address_ent-----*vendor
                           \
                            \          
                             \--*employee

where address carries a foreign key to addressable_entity, or something.  I haven't thought this one through particularly hard, but this is what these facilities are for ;-)

Cheers...

MikeA

-----Original Message-----
From: Louis-David Mitterrand
To: pgsql-general@postgresql.org
Sent: 2-14-01 6:35 PM
Subject: [GENERAL] using tables as types in other tables

In the app we are developing the concept of an address will occur very
often on many tables (vendor, clients, employees, etc.) so we are
looking to avoid code duplication by centralizing the addresses in one
table. However I once read on one of the pgsql- lists that one could use
a table name as a type:

create table address(street text, zip text, city text, country text);

create table employee(emp_addr address, emp_id int);

This is accepted by Postgres but the question is how to insert into the
employee table? What syntax should be used? I tried:

insert into employee values (('mystreet','myzip','mycity','mycountry'),
1);

But it doesn't work.

Should I proceed along that path or would I be better off using a
central address table with keys to the other tables?

Thanks in advance for your insight,

--
    THESEE: D'un perfide ennemi j'ai purgé la nature ;
            A ses monstres lui-même a servi de pâture ;
                                          (Phèdre, J-B Racine, acte 3,
scène 5)

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************