Table design

[samotek via AccessMonster.com]

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.

 

[Jeff Boyce]

Using a separate table for each competitor is what you'd probably have to do
.... if you were using a spreadsheet! Microsoft Access is a relational
database, and you won't get easy use of the relationally-oriented
features/functions if you feed it 'sheet data.

Instead of a separate table for each competitor, consider using a single
table, with an extra field. That extra field holds the competitor ID (which
you would find on a new table that lists competitors).

With this design, you don't have to change everything when you add/remove a
competitor, just add a row to the Competitor table.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[John W. Vinson]

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.

Several things here:

Attaching files is unwelcome and inappropriate. Some of the volunteers here
use *paid dialup* to connect to the newsgroups; having to pay to download a
multimegabyte file is not why we're here!

Secondly, your table design could be improved. One table per competitor is
simply not a good way to go. Also, Microsoft's Lookup Wizard is *VERY* limited
and neither necessary nor appropriate for this application: see
http://www.mvps.org/access/lookupfields.htm for a critique.

You would seem to have a case of a "many to many self join". Each one of your
products needs to be related to zero, one, or more other competitive products;
each competitor's project can be related to zero, one, and maybe more of
yours. One way to do this is to use two tables:

Products
ProductID <primary key>
ManufacturerID <link to a manufacturers table; your company is one entry>
ProductName <that manufacturer's name for the product>
<other fields about the product itself>

ProductLinks
ProductID1 <link to Products>
ProductID2 <also a link to Products>

For example, ProductID 131 might be your "Antifreeze Silstone B"; ProductID
428 might be Shell's equivalent. You'ld have a record with those two ID's in
ProductLinks.

I don't know the petroleum business well enough to know, but you might be able
to create a table of ProductTypes. If there is a single generic product -
"silicone modified antifreezes" - with multiple competitive examples, you
could consider each competitor's silicone modified antifreeze to be a special
case of the generic. Your Products table would then just have a link to this
ProductTypes table.

 

[Fred]

I come from background which is a little heavier on the business side of such
things.

If one took your question literally (including a presumption of a one-to-one
relationship, and that your intended use is one way (start with your product
and see what their equivalent is) and centered on your product (i.e. no need
to list their products except as the equivalent to yours) then just a big
flat table would do it. Then you have to figure out which of the above
assumptions are untrue and then fancy-up your structure to handle those
"departures". Trying to list answers for all of the possibilities would take
forever, but here are a few things to noodle on.

For example: If you are going to use it in the reverse direction (for
folks to convert their products to yours) the you probably have to think
about the fine points of what you mean by "equivalent". A simplifying
answer is that you are defining the ONE closest equivalent. But even this
answer can vary with which direction you are going. Brand "X" product "#1'
may be the closest thing they have to your product #2. But your product #3
might be the closes thing that you have to their product #2. Wrestling
with and answering these questions will be a more solid step #1 than crashing
into these questions after you've done all of the work.