Table design

[samotek]

I need an advice how to relate tables containing the lists
of the competitors against our list of products.The basis for our database is the table products. In our table products, which I am applying, we have listed all our products. Also, we have received the products for the competitors they are Shell,Mobil,etc.Each of these lists contains a lot of products. We have to enter them somehow in our database.I have to show the equivalents, or as we call them counterproducts of the other companies. For example our product is called Antifreeze Silstone B. The name of the shell product is different, I have to find it and connect it with the Lookup wizard. But also I have to find the counterproduct for Mobil in a similar way. And also for the other 6 competitors.I can easily do it for one customer, as I have shwon it.In my case it is Shell. But it is not enough. I also have to connect with the Lookup wizard for Mobil. How can I do it ? And also in the same way for the other competitors. So I think I should have mentioned that it concerns more the counterpoduts and way I could do it in the table products

P.S. sorry i couldnt attach the file, i dont know how to do it.But i have build the tables in the following way.Table Mobil, the autonumber is mobilid , the next table is Shell, the autonumnber is Shellid,and i conect them with my tble products.

 

[KenReay]

hmm

First of all I would not have seperate tables for each competirors list of products, since this means if a new competitor comes along you must add a new table. this may impact many queries

Rather I would have

a table of Competitors:

lngCompetitorId Autonumber PK
strCompetitorname

A table of Competitor products:

lngCompetitorProductid Autonumber PK
lngCompetitorId Long
strCompetitorProductNumber Text
strCompetitorProductDescription text
... etc

Since one of your products could have several competitor products, you need a linking table somthing like

lngYourProductId Long
lngCompetitorProductId Long

Using appropriate column names of course

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[samotek]

Thank you so much for you detailed recommendations.I understand now that my effort to use a separate table for each competitor is inappropriate.I also understand that i must have one table for the products with a field to identify whether it is my product or a competitors. Then i must also have a correspondence table that lists my product ID and each matching competitors.
I tried to follow the principle in your post but i cannot make my interface where to enter the products,i think this task is too difficult for me.Could you show me with your post how to do it with the following simple example:
My company name is called Meguin. One of our products is called Meguin 1234.Meguin 1234 corresponds to Shell alvania2 from the company Shell and Mobilgrease 2 from the Company Mobil.How could i enter these corresponding products in a query so that later on to be able to add on their prices ?Is it possible to illustrate this with an attachement?

 

[jedraw]

Samotek, Check this link for table design considerations:

http://r937.com/relational.html