:Re: data modeling question - child table with
multiple parents
yet, another solution:
add another table, called i.e. ACTOR (actor_id, actor_type);
sub-entity tables EMPLOYEE, SUPLLIER, CONTRACTOR will store
sub-entity
specific information, and their PK (employee_id, supplier_id
Bill,
you said :
gather the option of parent_table/parent_pk doesn't work for you?
if you mean, one column in the address table that holds the parent FK
values, how would you assign the foreign key to it? the only way to do that
would be programatically (thru the application) and not thru
I agree. If you have one client record and one supplier record put the
address there without the need for separate tables.
Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, August 01, 2002 1:02 AM
If this is the way you
Title: RE: data modeling question - child table with multiple parents
same
here - we don't reuse addresses so the table gets quite huge
-Original Message-From: Jacques Kilchoer
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, July 31, 2002
6:09 PMTo: Multiple recipients of list
modeling question - child table with multiple parents
yet, another solution:
add another table, called i.e. ACTOR (actor_id, actor_type);
sub-entity tables EMPLOYEE, SUPLLIER, CONTRACTOR will store
sub-entity
specific information, and their PK (employee_id, supplier_id, ...) will be
foreign keys
: data modeling question - child table with
multiple parents
yet, another solution:
add another table, called i.e. ACTOR (actor_id, actor_type);
sub-entity tables EMPLOYEE, SUPLLIER, CONTRACTOR will store
sub-entity
specific information, and their PK (employee_id, supplier_id
Good day, all:
Am curious to hear opinions on how to model a child table that has multiple
parent tables (i.e., foreign key to multiple parents)
Example:
There's a table that stores Addresses (table ADDRESS) for both employees
(table EMPLOYEE) and suppliers (table SUPPLIER).
Each of these
Bill,
simply combine the employee, supplier, contractor and vendor tables into one
such table titled people. Add a column to indicate what type of record it
is (a code indicating one of the above). solves your problem.
if this is not possible, then the address table could have multiple
thanks, tom . . .
your second option is one I had suggested below, which in may ways is
preferable - easy to track FK relationships, cascade delte, enforce r/i,
etc.
your first option won't work in our case - the parent tables are too
dissimilar to enable us to combine them - more real life
recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:data modeling question - child table with multiple parents
Good day, all:
Am curious to hear opinions on how to model a child table that has
multiple
parent tables (i.e., foreign key to multiple parents)
Example
Since the ADDRESS table is just a look-up table, why not let it have a
primary key for each address and then let the EMPLOYEE and SUPPLIER tables
reference it with a foreign key? That does not prevent the EMPLOYEE and
SUPPLIER tables from having their own unique primary keys.
-Original
model a child table that has multiple
parent tables
Consider reversing it. Model one parent (ADDRESS) and two child (CUST,
EMP). Parent is super-type that has the attributes common to both, e.g.,
name, address, city. Children have attributes unique to them, e.g., CUST
has sales_rep_id,
is easier to
manage.
HTH
Jared
Magaliff, Bill [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
07/31/2002 01:43 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:data modeling question - child table
:RE: data modeling question - child table with multiple parents
thanks, tom . . .
your second option is one I had suggested below, which in may ways is
preferable - easy to track FK relationships, cascade delte, enforce r/i,
etc.
your first option won't work in our case - the parent tables
yet, another solution:
add another table, called i.e. ACTOR (actor_id, actor_type);
sub-entity tables EMPLOYEE, SUPLLIER, CONTRACTOR will store sub-entity
specific information, and their PK (employee_id, supplier_id, ...) will be
foreign keys to actor_id in ACTOR table;
table ADDRESS will
Supplier
Supplier_id is pk
col2
col3
supplier_address_id fk references addresses (address_id)
Employee
=
Employee_id is PK
col2
col3
employee_address_id fk references addresses (address_id)
Addresses
=
Address_id is PK
zip code
street
etc
The address table is the
Subject: data modeling question - child
table with multiple parents
@lendware.com
here is an argument against this solution:
in current economy, some day you'll have a lay-off, and you'd want to
delete an employee (you will be laying off employees - not addresses,
right?), then you'll have to take additional care not to leave orphan
addresses, and all this headache, only
seems to me like a case of inverse logic.
is it employee (or supplier, or whatever) entity, which has address
attribute, or is it address entity, which has employee (or supplier)
as an attribute?
for me, it's the first: I'm not interested in any address, if it does not
belong to employee, or
Title: RE: data modeling question - child table with multiple parents
From: Igor Neyman [mailto:[EMAIL PROTECTED]]
here is an argument against this solution:
in current economy, some day you'll have a lay-off, and you'd want to
delete an employee (you will be laying off employees
Addresses is the parent (so it will not be orphaned). It's up to the
business rules to decide to keep addresses when a employee get fired or not.
If cleaning is required then it's easy to do.
Many of the application these days like to keep a table with all US
addresses.
The idea is always to
igor:
I agree with what you say here, as well as your previous comment about
deleting a laid-off employee and then the extra step of finding the orphan
addresses. Our developers have imbedded this inverse logic throughout the
application. and now I'm left to try to figure out how to validate
If this is the way you want it then why is the need for a separate table?
I understand separate table is good when you have repeating groups
(one-to-many) or it's an independent attribute/entity.
-Original Message-
Sent: Wednesday, July 31, 2002 6:27 PM
To: Multiple recipients of list
23 matches
Mail list logo
