Database Table Structure Design

[DPR01]

I am new to Access and have created Tables, Forms and Queries so have some familiarity with the program. However, I am not certain it is an effective or efficient way to handle my wants/needs.



I want/need a more efficient inventory management database process.
I make or buy a product that I use in my personal and professional life. The finished product has many different combinations of the components.
I buy 4 component types each having many different part IDs and assemble them into the desired finished-product configuration.
I track both component and finished-product quantities.
I have created a table for each component and one for the finished product.
I update the 5 tables via forms and update queries.

My question is: will my inventory system be more effective/efficient if I were to create a single table for both components and finished products?

I would like to create a Single Batch form to update the quantity of each component used and the quantity of finished product added.

Any input would be appreciated.

Thank you in advance.

[Minty]

It doesn't sound correct to have a table for each component type.
I would have thought you would have a single component table that had a component type identifier in it.

Perhaps you could post up a picture of your tables & relationship to give a better picture of the overall design.

[DPR01]

Thank you for response. I am self-taught and it seemed an easier way to learn and setup the desired elements. I was concerned about creating a single form to handle all the updates. Still not sure I could handle that part but will work at it if it makes sense from a structure perspective. I can just add a component type field and import the data into the new table based upon type.

Currently the tables are not related to each other. There is a form and query for each table.

[RayMilhon]

To make it more efficient look up 3rd Normal Form for Databases and apply those rules to your Database. Any questions you have on 3rd Normal Form can be answered here.

[DPR01]

Thank you for the input. I will look up the 3rd Form approach and let you know how it worked out or not.

Have a Blessed day.

[Welshgasman]

Here is a list that @Micron put together.

https://www.accessforums.net/showthr...553#post510553

[ArviLaanemets]

As it looks like this DB will be for personal use mostly, and isn't meant for production management, then easiest structure would be (on fly):
tblArticles: ArticleID, ArticleType, ArticleName, ArticleUnit, ... (where ArticleType may be like 0 for products, and 1 for components, and ArticleUnit may be pcs, kg, l, etc.);
tblProductStructure: ProductStructureID, ProductID, ComponentID, ComponentQty (Component can be either component type article, or product type article);
tblArticleTransactions: ArticleTransactionID, ArticleID, TransactionDate, TransactionType, TransactionSign, TransactionQty, Comment (TransactionType has smallint values for purchase with Sign = 1, for registering assembled product with Sign = 1, for using atricle to assemble a product with sign -1, for getting components back when disassembling the product with Sign = 1, for scrappint the article with Sign = -1, etc.)

The number of any article available at any time moment is the sum of quantities*sign of all transactions for this article until the date/datetime of this moment

As about form design, you can use a single form based on table tblArticles, with 2 continous subforms, one based on table tblProductStructure, and another on table tblArticleTransactions. Articles form can have a calculated control to display currently available quantity.