Database Design for lookup tables I have a transaction table called questionnaire and a question lookup table called question_codes.

questionnaire

qst_id -  number (primary key)
question_type -  number (foreign key)
question_category -  number (foreign key)


question_codes

code_id - number (primary key)
code_name - String 
code_desc - String 


Both tables are linked with qst_id and question_type,question_category are defined in question_codes lookup tables

I have a requirement to add a new column called product_category in questionnaire table and corresponding id,name and desc will be added to question_codes. product_category has a sub category column called product_type which will be displayed in questionnaire when product category is selected.

My question is do I need to create a new table to map product_category 
and product_type? Or can I use question_codes` table as lookup table?

Edit 1


I have table description and a few sample records in SQLFiddle
It really is a question of how your new data fits into the current model.

Option 1 - Same Data, need multiple associations

If your new "product category" data belongs to the same scope then you should put it into the question_codes table. And by scope, I mean would it look OK to see all of the data in question_codes table in a single drop down list in an application?

If you need to associate multiple rows of question_codes to questionnaire you should create an intermediate table between the two tables. This will require a migration of the existing associations into the new table.

    create table question_codes (
      code_id number,
      code_name varchar2(25),
      code_desc varchar2(25)
    );

    create table questionnaire (
       qst_id number
       /* , question_type number -- remove this column */
    );

    create table questionnaire_codes (
      qst_id number,
      code_id number
    );

    ALTER TABLE question_codes ADD (
      CONSTRAINT question_codes_PK PRIMARY KEY (code_id)
    );

    ALTER TABLE QUESTIONNAIRE ADD (
      CONSTRAINT QUESTIONNAIRE_PK PRIMARY KEY (QST_ID)
    );

    ALTER TABLE questionnaire_codes ADD (
      CONSTRAINT questionnaire_codes_PK PRIMARY KEY (qst_id, code_id),
      CONSTRAINT FK_ques_codes_question_codes FOREIGN KEY(code_id)
        REFERENCES question_codes (code_id),
      CONSTRAINT FK_quest_codes_questionnaire FOREIGN KEY(qst_id)
        REFERENCES questionnaire (qst_id)
    );

    insert into question_codes values (1,'QST1','Is Tangible');
    insert into question_codes values (2,'QST2','Is Perishable');
    insert into question_codes values (3,'QST3','Exported?');
    insert into question_codes values (4,'QSC1','Vendor Rating');
    insert into question_codes values (5,'QSC2','Vendor Payment');
    insert into questionnaire values (1);
    insert into questionnaire values (2);
    insert into questionnaire values (3);
    insert into questionnaire_codes values (1, 1);
    insert into questionnaire_codes values (1, 3);
    insert into questionnaire_codes values (2, 2);
    insert into questionnaire_codes values (3, 3);
    insert into questionnaire_codes values (3, 4);
    insert into questionnaire_codes values (3, 5);

    commit;


Option 2 - Different data

If your new "product category" data is a bit different than the existing data in question codes, you should create a new look-up table. Meaning, you would expect to see two separate drop down boxes in an application for questionnaire.

Associate the new look-up table with the questionnaire table.

    create table question_code (
      code_id number,
      code_name varchar2(25),
      code_desc varchar2(25)
    );

    create table question_category (
      cat_id number,
      cat_name varchar2(25),
      cat_desc varchar2(25)
    );

    create table questionnaire (
       qst_id number,
       code_id number,
       cat_id number
    );


    ALTER TABLE question_code ADD (
      CONSTRAINT question_codes_PK PRIMARY KEY (code_id)
    );

    ALTER TABLE question_category ADD (
      CONSTRAINT question_category_PK PRIMARY KEY (cat_id)
    );

    ALTER TABLE questionnaire ADD (
      CONSTRAINT QUESTIONNAIRE_PK PRIMARY KEY (QST_ID),
      CONSTRAINT FK_quest_question_codes FOREIGN KEY(code_id)
        REFERENCES question_code (code_id),
      CONSTRAINT FK_quest_question_category FOREIGN KEY(cat_id)
        REFERENCES question_category (cat_id)
    );


    insert into question_code values (1,'QST1','Is Tangible');
    insert into question_code values (2,'QST2','Is Perishable');
    insert into question_code values (3,'QST3','Exported?');
    insert into question_category values (1,'QSC1','Vendor Rating');
    insert into question_category values (2,'QSC2','Vendor Payment');
    insert into questionnaire values (1, 1, 2);
    insert into questionnaire values (2, 2, 2);
    insert into questionnaire values (3, 3, 1);

    commit;


Avoid Option 3 - Common lookup table

You can create a common lookup table that many tables can use at the expense of data integrity. If you go this route, you will have more bugs in your data and applications. Avoid this unless you have a time-to-market issue and plan to refactor after you get your angle investors.

    create table code_category (
      code_category number,
      description varchar2(25)
    );

    create table question_code (
      code_id number,
      code_category number,
      code_name varchar2(25),
      code_desc varchar2(25)
    );


    create table questionnaire (
       qst_id number,
       vendor_code_id number,
       type_code_id number
    );


    ALTER TABLE question_code ADD (
      CONSTRAINT question_codes_PK PRIMARY KEY (code_id)
    );

    ALTER TABLE code_category ADD (
      CONSTRAINT code_category_PK PRIMARY KEY (code_category)
    );

    ALTER TABLE questionnaire ADD (
      CONSTRAINT QUESTIONNAIRE_PK PRIMARY KEY (QST_ID),
      CONSTRAINT FK_quest_vendor_codes FOREIGN KEY(vendor_code_id)
        REFERENCES question_code (code_id),
      CONSTRAINT FK_quest_type_code FOREIGN KEY(type_code_id)
        REFERENCES question_code (code_id)
    );

    insert into code_category values (1, 'Question Types');
    insert into code_category values (2, 'Vendor Types');
    insert into question_code values (1, 1, 'QST1','Is Tangible');
    insert into question_code values (2, 1, 'QST2','Is Perishable');
    insert into question_code values (3, 1, 'QST3','Exported?');
    insert into question_code values (4, 2, 'QSC1','Vendor Rating');
    insert into question_code values (5, 2, 'QSC2','Vendor Payment');
    insert into questionnaire values (1, 1, 4);
    insert into questionnaire values (2, 2, 4);
    insert into questionnaire values (3, 3, 5);

    commit;


Excuse me while I take a shower after writing that last example.