> > employees and customers can be the same and it
> > happen enough that it needs to be considered in
> > application.
> > in that case, i'd use a link table to link them
> > together.
> > my problem is that i don't want to repeat data
> > in the table structure and the only way i can
> think of
> > doing that is to have a persons table with all
> > people and then have clients and employees tables
> > 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
> > 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
> 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:
> The nice thing is that you can easily put relate
> table between two entities like lenders and
> employees to track this information.
> 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?
Yahoo! Music Unlimited
Access over 1 million songs.
In response to
pgsql-novice by date
|Next:||From: Greg Quinn||Date: 2006-11-26 07:53:04|
|Subject: Re: Inserting values into a variable table|
|Previous:||From: operationsengineer1||Date: 2006-11-26 06:43:50|
|Subject: Re: Integer Question - Does Limit Value Make Sense|