Best way to contruct a many-many table/form

[Raymas]

Hi All,

My question is more to get a second opinion (or two or three..). I'm constructing a complete database for my company. I have developed forms and tables for collecting products and constructing product codes. So basically i have a table of all the products and a table of all the materials used to make those products (its a little more complicated than that of course). Now I wish to make a form/table so that the user can list all the material and quantities that go into making a product.

My question is what is the most efficient way of doing this? So far I have thought of two options, my first was to have a record for every product with a single field holding all the materials together, however there can be up to 30-40 materials per product, and i've read this method isn't the best. My other option was to have a record for every product/material join in a many-many table, does this seem like im complicating it too much. Of course the table storing this data is in the background but it could grow to 30,000 records using this method. Thanks in advance for your input.

Regards,

Mike

 

[jdraw]

There are a number of data models here.
http://www.databaseanswers.org/data_models/index.htm

Some of these may give you some ideas.

If you have test data, you should work with a data model. Try to stump the model -- that is if your model doesn't support the test data, then adjust the model as necessary.

I would suggest you get the model (tables and relationships right) and then focus on the forms etc.

Just my 2 cents...

 

[gemma-the-husky]

a production management environment is probably the hardest possible database to pick. No doubt you want to include stock/inventory control also

Maybe your requirements are not quite as advanced as this


But if they are - then
a) you will find that that a ready built packaged solution is probably very expensive and prohibitively complex, and/or
b) trying to build it yourself is just as difficult


I think you need to specify exactly what you want very carefully. Maybe you need some professional help in the specification/design face.

Sorry - but this really is quite advanced stuff, and in many ways, if you have to ask how to do this, then you will struggle making any progress.

 

[Raymas]

Thanks for your speeding responses. My main concern was the size of the table, as I'm new to access and unsure of its contraints. But I have looked around, and thinking that maybe 30,000 entries really isn't that much. So I have gone with the straight forward many-many table. Everything just fell into place after that, and works great. I must admit I'm pretty happy with the way the form turned out also .
In the end form allows user to enter materials for a product using drop down boxes to select the product and relevant material criteria, and see them being added using a filtered list box, and even remove or edit materials using the list box. My next task will be now looping this back around to sales and developing a costing for each product based on the materials, I don't think this should be very hard. Anyway thanks again!

Raymas