On a system that you expect to have heavy inserts and/or deletes, prototyping this and testing it would be a good idea.
I haven't done that myself. If it ever caused me a problem. I was blissfully ignorant. :) Jared On Thursday 01 August 2002 11:18, Magaliff, Bill wrote: > jared: > > any thoughts on the point at which this becomes a potential performance > bottleneck? pretty simple if there are two potential fk's as in your > example - but what about 4 or 5 fk id's? > > -biill > > -----Original Message----- > Sent: Thursday, August 01, 2002 1:48 PM > To: Multiple recipients of list ORACLE-L > > > btw, in your first solution, how are you going to implement mutual > > exclusiveness of EMPLOYEE_ID and SUPLIER_ID? trigger? - not very > > elegant. > > Actually quite simple and elegant: > > alter table add constraint only_one > ( check ( > ( employee_id is null and supplier_id is not null ) > or > ( supplier_id is null and employee_id is not null ) > ) > ) > > Jared > > > > > > > "Igor Neyman" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 07/31/2002 02:20 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > cc: > Subject: 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, ...) will be > foreign keys to actor_id in "ACTOR" table; > table "ADDRESS" will reference "ACTOR" table (not multiple sub-entities), > and you can enforce this relationship in the database; > thus, adding new sub_entity (like "VENDOR") will not require any changes > in > existing tables; > also, you can implement "TELEPHONE" table the same way (referencing > "ACTOR" > table). > > btw, in your first solution, how are you going to implement mutual > exclusiveness of EMPLOYEE_ID and SUPLIER_ID? trigger? - not very elegant. > > Igor Neyman, OCP DBA > [EMAIL PROTECTED] > > > > ----- Original Message ----- > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Wednesday, July 31, 2002 4:43 PM > > > 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 tables has a Primary Key field called ID. > > > > One way to set this up would be for the ADDRESS table to have 2 fields, > > EMPLOYEE_ID and SUPPLIER_ID, which would be mutually exclusive (i.e., > > one > or > > > the other, to indicate the parent record of the address). > > > > Another solutions if for the ADDRESS table to have two fields to > > indicate > > > the parent table name and parent table pk value. > > > > The first method enables me (the dba) to create foreign keys from the > > address table to each of the parent tables to validate data. The second > > method does not enable me to create such foreign keys (leaving it to the > > developers to validate date and insure referential integrity) but would > > also > > > easily facilitate the addition of other parent tables (e.g., CONTRACTOR, > > VENDOR, etc.) without altering the ADDRESS table itself. > > > > Any and all thoughts, comments, opinions, experiences are most welcome. > > > > Thanks! > > bill magaliff > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Magaliff, Bill > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California -- Public Internet access / Mailing Lists > > -------------------------------------------------------------------- > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
