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: Igor Neyman 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).
