Q:Oracle Sequence vs Composite Primary Key
Date: 1997/06/04
Message-ID: <hiYRYApGRok6_at_mcrcr6>#1/1
I am in the process of finalizing a moderately large E-R model for an Oracle database and have questions re "meaningless" Oracle sequence primary keys vs. composite keys. Following are just 4 extracted entities from the model:
Patient:1 --> N:Problem_Assessment:1 --> N:Treatment:1 --> N:Response
The current primary keys for each of the above are:
Patient: patient_id
Problem_Assessment: patient_id + doctor_id + problem_cd + problem_dt
Treatment: same as Problem_Assessment + treatment_type + treatment_date
Response : same as Treatment + response_type + response_date
(There are actually several more tables that are either in between or
that feed off of the above, so the composite keys get even longer.)
If I use system-generated Oracle sequence primary keys, is it this sequence number that then becomes the foreign key for just the 1 child below and the basis for any "joins" of these tables? If so, then if the user simply wants to know what Responses a given named Patient has had, ALL of the tables from Patient to Response have to be joined to extract this info.
(vs. with composite keys, since pat_id is in the primary of both the
Patient and Response (inherited as foreign key) tables, I could just join
these 2 tables to extract the patient name and his responses).
Therefore, in order to alleviate MULTI-table joins in such scenarios, should I allow definition of all/some (whole/partial?) frequently-referenced former compososite key fields as "shadow" fields (i.e. pat_id) in the child entities (i.e. Treatment, Response) so that users can query tables via alternate keys that I'd have to set up for these fields? The interface will have to handle the real "meaningless" sequence keys in the background while presenting the alternate keys to users.
Is it relationally "legal to accomplish this by directly "relating" Patient separately to each of the entities (Problem, Treatment, Response, etc) with which there would frequently be joins during user-queries since all of these entities are essentially dependent on Patient and one always wants to know the patient name for each query? This would allow pat_id to be inherited by each of these entities as a foreign key.
(This approach seems relationally redundant and might require
a lot of the "cascade" referential integrity maintenance which adversely
affects performance in a multi-composite primary key scenario.)
Any advice re the above would be greatly appreciated as I am under time pressure to finalize the model, generate the Oracle database, and start developing the front-end app ASAP. Thanks.
+----------------------------------------------------------------------------+|Frieda Pavel, Systems Mgr, Research Computing Resource, NYU Medical Center | |E-Mail: PAVELF01_at_MCRCR.MED.NYU.EDU
+----------------------------------------------------------------------------+Received on Wed Jun 04 1997 - 00:00:00 CEST