Thread: 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
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
> 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. Are you trying to find a way to relate an employee to one or more customers? Regards, Richard Broersma Jr.
> 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,
a customer can also be an employee. one application
i'm working on is for a loan broker and employees can
get loans through this broker.
i'm looking for a way to be able to model this while
not repeating data entry.
> 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.
iiuc, this means the data for a person that is also an
employee is duplicated - one time in t_person and one
time in t_employees, right? my gut feel says to avoid
the duplication, but maybe it is the lesser of two
evils and i'm just not sure where to draw that line in
the sand.
> 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.
iiuc, this duplicates data (once in employee and once
in customer for those folks who are customers and
employees), too.
> 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.
this link table would be in place to only list the
dual role people - customers and employees, right?
for some reason, this doesn't approach feel right.
> 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.
employees and customers can be the same and it will
happen enough that it needs to be considered in the
application.
Richard, no, i'm not relating and employee to one or
more customers. i may well end relating one or more
employees to a transaction that may have one or more
customers (clients).
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.
however, this isn't jumping out to me as the obvious
best way to do this.
Brandon and Richard, thanks for taking the time to
chime in here.
____________________________________________________________________________________
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail beta.
http://new.mail.yahoo.com
> 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.
> > 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
> 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? If you are referring to a generalization hierarchy, you are using tables with primary keys and foreign keys... However, each table has a one to one relationship. Modeling a parts catalog is a problem lends itself well to a generalizatoin hierarchy. At the (highest level)/(most general) table of the hierarchy would be a simple parts table all of the information in this table is common for all parts in a catalog: tbl_Parts: PartNo | part_type | description | unit_cost | -------------------------------------------------------- 1 | motor | .25 hp DC motor | 50.00 | 2 | paint | silver spray can| 2.00 | 3 | motor | piston pump mtr | 40.00 | 4 | paint | gray 1gal latex | 15.00 | 5 | relay | 4PDT 24vdc relay| 20.00 | This is a simple table but it doesn't allow you hold specific data about each type of part. In this case you have three options. you can increase the size of you description field to around a thousand characters so you have room to add a detail description, but this can be inconvient and ugly to deal with. or you can nullable column to this table that you filled in when it applies to a specific part. This works well as long as you don't have many types of parts that require a specific field and you only need a few nullable column to hold the additional details. In the case of a parts table, I would like to have alot of additional columns to specify additional detail about each part, and I could have hundreds of diffent part types. So this in case, I would like to put the additional information the (lower level child/more specific) auxillary tables. Since I am only showing 3 part types I will only create 3 additional tables: Motor, Paint, relay: Motors: PartNo|part_type|voltage | HP | SF | -------------------------------------- 1 |motor | 48VDC |.25 | 1.5 | 3 |motor | 120VAC | 5 | 1.25 | if you joined tbl_parts to Motor you like: select partNo, part_type, description, Price, voltage, hp, SF from tbl_parts as P join Motors as M on ( P.partNo, P.part_type) = ( M.partNo, M.part_type) ; PartNo|part_type|description |unitc|voltage | HP | SF | ---------------------------------------------------------- 1 |motor |.25 hp DC motor|50.00| 48VDC |.25 | 1.5| 3 |motor |piston pump mtr|40.00| 120VAC | 5 |1.25| You use the same technique to create specific detail table that relate back to the main parts table. I would do this for the paint table and relay tables. At this point ( having created the motor,paint,relay table) I can start building relationships between these child tables. Lets say you wanted to track which relays that were sold with a certail motor, you would create a relate table between the two. motors --------->[ rlt_motor_relays]<----------- relays. To help explain it further you can read about the postgresql extenstion that supports this: http://www.postgresql.org/docs/8.2/interactive/ddl-inherit.html Regards, Richard Broersma Jr.
Hi, The second structure is more normalised and it's a good rule of thumb to normalise your data. Does anything else need to be split out? For instance can a person have more than 1 address? Do you need an address history? Regards Duncan