Re: Data (Table) Structure Question

> 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