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

> employees and customers can be the same and it will
> happen enough that it needs to be considered in the
> application.
> in that case, i'd use a link table to link them all
> together.
>
> my problem is that i don't want to repeat data entry
> in the table structure and the only way i can think of
> doing that is to have a persons table with all known
> people and then have clients and employees tables that
> have a foreign key linked to the relevant persons
> table id.

This wouldn't create duplicate data in you database.  I believe
that Brandon is referring to a data model know as a
(generalization/inheritance) Hierarchy. This works by vertically
partitioning your data. I'll explain more below.


>
> however, this isn't jumping out to me as the obvious
> best way to do this.

There are two ways that I know of to do this:
1) a recursivly joined table
In this model you would only have one table that holds all people.
you would have one column that that is a foreign key that referenced the primary key of another
record. i.e.:

pkey  |  fkey  |  title  |   name | other | info |
1     |null    |lender   |dave    |       |      |
2     |null    |lender   |ted     |       |      |
3     |1       |emp      |jeff    |       |      |
4     |2       |emp      |sam     |       |      |

so if you wanted to know all of the people that a lender was loaning to:

select L.name as lender, E.name as emp
from t_person as L left join t_person as E
on (L.pkey = e.fkey);

This works okey as is very easy to set up, but doesn't scale well
with changing requiredments.


The other method is to use some sort heirarchy.  you can do this using
a postgresql extenstion called table inheritance. see the create table syntax
in the manual, or you can set up your one using vertically partitioned tables
that are linked together.  I read a little about this data model in ERD modeling books and
my SQL for smarties book that I am currently reading.

Using vertical partitioning, you would have one person table that holds information
that is common to all people.  Next you add sub-set tables Like employee, lender, bosses,
customer, vendor, that only hold extenstion data that would be not-applicable or beyond the scope
of a person.  Where applicable these tables would have a 1 to 1 link back to the person table.

I.e.  a person doesn't necessarly have
      but the ones linked to employee do have one in the employee table

      a person doesn't necessarly have
      credentials for lending but a person
      linked to the lender table does and
      the lender table hold this credentials.

This model requires alot more work to set up, but is can easily scale up to almost anything that
you throw at it.

I hope that this explains a little.  it is hard to explain it without the aid of a diagram.
This link might help:
http://uml.visual-paradigm.com/uml/Object_Relational_Mapping/New_Inheritance_Strategy_(Table_per_subclass)

The nice thing is that you can easily put relate table between two entities like lenders and
employees to track this information.

Regards,

Richard Broersma Jr.