RE: Single Code Table or Separate Code tables dilemma

I hate to be picky, but I don't like either of the solutions proposed below. For the first solution, that means that I would have to have the repeating values 'SPLT' and 'TYPE' in every row of the DOCTOR table, which is a table with a lot of rows in it.

For the second solution, I have to have a table that contains every possible combination of SPLT and TYPE.

Both solutions seem to be wasteful and clumsy. Please don't take this as a direct criticism of you, but I still don't see how a single code table can be implemented well using FK relationships.

> -----Original Message-----
> From: Mandar Ghosalkar [mailto:MandarG_at_gsr-inc.com]
>
> CREATE TABLE I_DONT_LIKE_MASTER
> ( CODE_TYPE VARCHAR2(4) NOT NULL,
> CODE_VALUE VARCHAR2(3) NOT NULL,
> CODE_DESC VARCHAR2(20) ,
> PRIMARY KEY (CODE_TYPE, CODE_VALUE) )
>
> SPLT  N       SDFLJDSL
> SPLT  FP      SDFSDKFDS
> SPLT  OBG     SFLSDJFSD
> TYPE  MD      SDFLSDFSDF
> TYPE  FP      SDFJDSFJ
> TYPE  OPH     SDLFJDSKF
>
>
> CREATE TABLE DOCTOR
> (.....,
> SPECIALITY_CODE_TYPE  VARCHAR2(4),
> SPECIALITY_CODE_VALUE VARCHAR2(3),
> TYPE_CODE_TYPE                VARCHAR2(4),
> TYPE_CODE_VALUE               VARCHAR2(3),
> .....,
> foreign key  (SPECIALITY_CODE_TYPE,SPECIALITY_CODE_VALUE) references
> I_DONT_LIKE_MASTER,
> foreign key  (TYPE_CODE_TYPE, TYPE_CODE_VALUE ) references
> I_DONT_LIKE_MASTER )
>
> D1    SPLT    N       TYPE    OPH
> D2    SPLT  N TYPE    MD
>
>
> but u can also create surrogate key and make (CODE_TYPE,
> CODE_VALUE) as
> alternate key (unique) and then reference the surrogate key
> in ur doctor
> table, instead of two cols as one foreign key
>
> -----Original Message-----
>
> I have a table called DOCTOR that has two fields - SPECIALTY
> and TYPE.
> The acceptable values for SPECIALTY are N, FP, OBG, etc...
> The acceptable values for TYPE are MD, FP, OPH, etc...
> If I have a "single code table", how can I create a Foreign
> key constraint
> on the SPECIALTY column and another one on the TYPE column?
Received on Fri Mar 23 2001 - 20:21:21 CST