locked
OLTP Design Question RRS feed

  • Question

  • I have a design question regarding an OLTP system.

    I have the following lookup tables and a mapping table between them.
    I need to store Drugs associated to Program and Manufacturer.
    Which would be a better option design wise,
    1. Store ProgramManufacturerID in Drug table.
    2. Store both ProgramID and ManufacturerID in Drug table.

    Please let me know...

    Program_LKP
    ProgramID INT IDENTITY(1,1) NOT NULL
    ProgramName VARCHAR(255) NOT NULL
    InactiveFlag CHAR(1) NOT NULL
    CreatedByID INT NOT NULL
    CreatedDate DATETIME NOT NULL
    ModifiedByID INT NOT NULL

    Manufacturer_LKP
    ManufacturerID INT IDENTITY(1,1) NOT NULL
    Manufacturer VARCHAR(255) NOT NULL
    InactiveFlag CHAR(1) NOT NULL
    CreatedByID INT NOT NULL
    CreatedDate DATETIME NOT NULL
    ModifiedByID INT NOT NULL
    ModifiedDate DATETIME NOT NULL

    Program_Manufacturer
    ProgramManufacturerID INT IDENTITY(1,1) NOT NULL
    ProgramID  INT NOT NULL
    PapManufacturerID INT NOT NULL

    Option 1
    Drug
    DrugID   BIGINT IDENTITY(1,1) NOT NULL
    ProgramManufacturerID INT NOT NULL


    Option 2
    Drug
    DrugID   BIGINT IDENTITY(1,1) NOT NULL
    ProgramID  INT NOT NULL
    ManufacturerID  INT NOT NULL


    Thanks in advance

     

     

    Monday, November 16, 2009 8:41 PM

Answers

  • It depends on your queries and your data design. I think Option 2 would give you more flexibility if you want to get the the Manufacturer table or Program Table. If you have a Manufacturer and no program, and if you have a drug from that manufacturer, you can't access it unless you allow NULL in your program_Manufacturer table for PapManufacturerID. But I guess you have everything set to NOT NULL so it doesn't really matter.
    Also, if you choose to use Option 2, it's better to create your indexes on the ProgramID and manufacturerID since you might be joining on both columns most of the time.

    Abdallah, PMP, ITIL, MCTS
    • Marked as answer by Zongqing Li Monday, November 23, 2009 8:28 AM
    Monday, November 16, 2009 8:52 PM