Access Table design

need some help with tables/relationships.

[Andrew Thomas]

Im trying to develop a 'build to order' system, where users can select components to create their own computer.

Im having trouble getting my head around the database design, im using Access.

I understand about many-to-many relationships, and that they need to be resolved, but i cant put it onto paper!

I have 2 tables, so far

Products
Product_ID = Auto number (FK)
Product_Code = text
Product_title = text

Components
Component_ID = Auto number (FK)
Component_Code = text
Product_Code = text
Component_title = text
Component_Price = currency

One PRODUCT can have many components
AND COMPONENTS can belong to more than one product.

Do i need a link table?

How do i reduce data duplication?

If anyone can advise me on how to setup my tables and relationships, id be gratefull. I used to do this years ago, but my mind has gone blank.

thank you

[ukgimp]

If you are doing this in access you will need a linking table that contains all the products and components combinations

PRODUCTS
P_ID
P_Desc

COMPONENTS
C_ID
C_Desc

PODUCTS_COMPONENTS
P_ID (FK)
C_ID (FK)

In access you drag the primary keys of products and components to the linking table.

Then when you wish to find all the elememts of of one product you find all the components in the linking table that have a product id that you search for

You may wish to add additinal linking table to both the components and products table that contain suplier and buyer info.

HTH a little

[SethCall]

Ill take a stab at helping too:

Just use access's visual query creation mode (its the easiest to get your first query up: then change to SQL view to get the generated SQL... you can take a look at what its doing)

anyway, obviously your componenets and products should be in different tables, with the product id being the primary key in your 1st table(something like a UPC or BRand + Model number is a good choice, as you want your key to contain no duplicates.
Then in your second table, have your component UPC or brand model be your key there.

Anyway, your tables, at their thinnest can look like this:
Table1: Table2:
ProductUPC ComponentUPC
ComponentUPC CompentDescription

Well, in that graphical query creation mode, choose your 2 tables, and notice they already have a black line drawn from table to table. Its probbaly not in the right stop. MAke it go from table 1(componentUPC) to table2(complentUPC).

Now execute the query. YOu should have multiple rows per prodcut where u have multiple components fro that product, in table1.

Good luck.

[Andrew Thomas]

thanks for the help :)