Hi,

Having trouble with relationships and Table Structure for a Database. Please help as am about to give up.

The database is to monitor trades. Each trade has a product type for which it has different characteristics (requires different fields)

So in terms of fields:

Tbl Main:

Trade Number (AutoNum) (Prim Key)
Trade Date (Date)
Trader (Txt)
Counterparty (Txt)
Allocation (Num)
Product Type (Num)

Now if Product Type is type 1 then I need:

Product Type (Num) (Prim Key)
Underlying (Num)
Price (Num)
Notional (Num)
Level (Txt)
Legal (Txt)

But if product type is 2 then I need

Product Type (Num) (Prim Key)
Underlying (num)
Maturity
Strike
Opt Type

I am thinking of using a table for each product type with all the details but cant figure out what relationships to use and how to structure the tables. I would rather not use just one large table for product and have it contain empty/redundant fields - but if this is the best way to do it then I guess I will have to!

I guess because each trade will have a unique trade type and unique details in for the product that a one to one relationship is used anbd maybe using primary key of trade number?

All the other relationships I have sorted - just this one.

Please can you offer some advice?

thanks,