Re: Data (Table) Structure Question - Mailing list pgsql-novice

I believe the preferred way to do this is to use sub-types.  Create a
t_person table.  Make a type field if you wish with 'E' for an employee
or 'C' for a customer, or you can use implicit typing from joins
('t_person inner join t_employee' will always get only employees, etc.).
You might even wish to create views from these tables to make
presentation easier.

The other option is to treat employees and customers as different and
unrelated entities entirely.  Then you'd make a t_employee table and a
t_customer table, and there would be no t_contacts or t_person table.

In this latter case if you happen to have some employees who are also
customers, then you'd make a table, say t_customer_employee, which has
two fields: one the primary key of t_employee and the other the primary
key of t_customer.  Then you make each one a foreign key to their
respective table.  That's how you do a many-to-many or
zero/one-to-zero/one relationship, which is what this is.

It depends entirely on whether your business logic ever needs to treat
customers and employees the same, and how often you need to do that.
You can, after all, do a distinct union if you only need to do this very
rarely and the field names in your tables are similar enough, but if you
need to do it a lot then you should design the database accordingly.

--
Brandon Aiken
CS/IT Systems Engineer

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of
operationsengineer1@yahoo.com
Sent: Friday, November 24, 2006 5:11 AM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Data (Table) Structure Question

i hope everyone's thanksgiving was grand.

i have contacts, customers and employees that i want
to manage in a db application.

i can think of two ways to do this.

first, have a table for each and fill each with all
the relevant data:

1. t_custoemrs
2. t_employees
3. t_contacts

second, have a contacts table that records data that
is repeated throughout each table (ie, name, address,
phone, etc...)

i could then have a t_employees table with a foreign
key to the t_contacts table id.  this table would only
have employee related information and would link back
to contacts.

t_customers would do the same - list only customer
specific information.

i think the latter way is the way to go, but i'm not
sure.  i don't particularly like t_contacts as the
name of the general table.  maybe i need to name it
persons.

anyway, i'd value the input of some more experienced
developers.

tia,

oe1



________________________________________________________________________
____________
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail beta.
http://new.mail.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings