Product Matrix DB Structure Query

Just need pointing in the right direction

[woldie]

Hi all,

I've got this interesting project for a client, and just need some direction on this one, I've made a start on the product matrix DB structure but anyone who thinks I'm on the wrong track then any assistance would be much appreciated.

Basically I need to provide a figure on the information provided below:

Very low adverse
VL1 VL2 VL3
Sub Prod 1 6.5% 6.5% 6.5%
Sub Prod 2 1.00% 1.00% 1.00%
Sub Prod 3 7.5% 7.5% 7.5%
Sub Prod 4 7.3% 7.3% 7.3%

Low Adverse
LA1 LA2 LA3
6.5% 6.5% 6.5%
1.00% 1.00% 1.00%
7.5% 7.5% 7.5%
7.3% 7.3% 7.3%

Example: if you select LOW ADVERSE, LA2 then SUB PROD 3, you should get the value 7.5%

Note that this matrix goes along the page.

So my DB Structure is as follows:

In this table I will place 'Very low adverse' and 'Low Adverse'

prod_type

pid int pri key
prod_type varchar

In this table I will place 'Sub Prod 1' and so forth......

sub_prod_type

sbid int pri key
sub_prod_type varchar

prod_price

prid int pri key
pid int (FK)
sbid int (FK)
VL1 varchar
VL2 varchar
VL3 varchar
LA1 varchar
LA2 varchar
LA3 varchar

Now I'm not quite sure if I'm on the right track with the prod_price table, or if I should do for each product.

Example:

V_low_adverse_prod_price

prid int pri key
pid int (FK)
sbid int (FK)
VL1 varchar
VL2 varchar
VL3 varchar

Low_Adverse_prod_price

prid int pri key
pid int (FK)
sbid int (FK)
LA1 varchar
LA2 varchar
LA3 varchar

Any ideas?

Many Thanks

[coopster]

Bear with me, I don't understand the entire project here, but is there any reason you couldn't have your product price table be as simple as...

pid int (FK) 
sbid int (FK) 
price

[woldie]

Hi Coopster,

Thanks for the reply.

As for your suggestion, that sounds the kind of thing I'm looking for. I'll let you know if there are any further developments.

If there is anything else you would like to add, then feel free.

Cheers