OLTP Design 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 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
DrugID BIGINT IDENTITY(1,1) NOT NULL
ProgramID INT NOT NULL
ManufacturerID INT NOT NULL


Thanks in advance

> Which would be a better option design wise,
1. Store ProgramManufacturerID in Drug table.
2. Store ProgramID and ManufacturerID in Drug table. <

What about just putting the manufacturer_id into the drugs table. The
path to the "program" you will find in your Program_Manufacturer
table.

There are also some additional point to research on the primary keys.
Most likely you will find an industry standard for the drugs and the
manufacturers, which would be a better choice than IDENTITY. Also I
can not see a point for the ProgramManufacturerID in the
Program_Manufacturer table (the proper primary key is a compound one
of the both columns program_id and manufacturer_id).

brgds

Philipp Post