Lets say
I have 1 department which have 4 suppliers
supplies 4 different types of product (A,B,C,D)
my main objective is to capture rating( 20 types) of these different types of product in alphabet and also in numerical thru lookup table
I created following tables
Supplier( supplierid, supplier)
01 ABC
02 DEF....
Suppliertype( suppliertypeid, supplierid, typesof products)
01 01 A
02 01 B
03 01 C
04 01 D
05 02 A ........
I created thirdtable
Ratingtable(departmentid,suppliertype,rating)
Dept 01 AAA
Dept 02 AA
Dept 03 AAA
Am i on right track and beside that I also have to put numerical value for rating, where is the best place to add column for numerical rating?
Can i add rating and numerical value to suppliertype table and taking off rating from rating table?
There are 20 types of rating with numerical value ( in look up table)