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.
>

Richard, the latter method makes much more sense in my
mind.  i don't think scaling will be an issue (i'd
*love* to have *that* problem!), but i want something
that makes sense to me.

however, i don't understand what an extenstion would
do.  wouldn't using normal tables with apporpriate
primary / foreign key links work just fine?

tia...



____________________________________________________________________________________
Yahoo! Music Unlimited
Access over 1 million songs.
http://music.yahoo.com/unlimited