Skip site navigation (1) Skip section navigation (2)

Re: Data (Table) Structure Question

From: <operationsengineer1(at)yahoo(dot)com>
To: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>, Brandon Aiken <BAiken(at)winemantech(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Data (Table) Structure Question
Date: 2006-11-26 07:04:39
Message-ID: 329303.1240.qm@web33303.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: 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

In response to

Responses

pgsql-novice by date

Next:From: Greg QuinnDate: 2006-11-26 07:53:04
Subject: Re: Inserting values into a variable table
Previous:From: operationsengineer1Date: 2006-11-26 06:43:50
Subject: Re: Integer Question - Does Limit Value Make Sense

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group