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)

Hello

... "There are 20 types of rating with numerical value ( in look up table)"

How is rating related to numerical value(s) ? Something like this:

AAA 20.5
AA 5.5

or even like this:

AAA 20.5
AAA 30
AAA -1 etc ?

krs,
tesu

p.s.
I have got the feeling that there could be something wrong between Supplier( supplierid, supplier) and Suppliertype( suppliertypeid, supplierid, typesof products)

If you want to express that: One supplier supplies various types of products and on type of product is supplied by various suppliers there exists a many-to-many relationship between suppliers and producttypes. Therefore the "linking" table contains primary key from supplier table and primary key of table producttypes. Both primary keys form the primary key of your so called "Suppliertype" table. They must also be foreign keys in that table. Sure, there can be additional data in the linking table, sometimes even an third attribute can be part of its primary key. So the create-table statement for Suppliertype would look like:

create table Suppliertype(supplierID integer not null, producttypesID integer not null,
maybe_additional_data varchar(50), primary key (supplierID, producttypesID),
foreign key (supplierID) references suppliers,
foreign key (producttypesID) references producttypes);

btw, if you omit the word "types" in "...of these different types of product...", what then means that a supplier supplies various (real) products and a (real) product would be supplied by various supplier, the many-to-many relationship will turn a bit more meaningful:

create table ProductSupplier(supplierID integer not null, productID integer not null,
maybe_additional_data varchar(50), primary key (supplierID, productID),
foreign key (supplierID) references suppliers, foreign key (productID) references products);

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.