Question on database design

[marcenmoni]

I have some experience with Access including programming in modules. But my background is architecture, not database design, and I'm stumped by this design problem.

I'd like to set up a simple database for inventory tracking. I have three basic tables with the following fields:
1) purchases: purchase date; category; stock description; quantity; cost
2) inventory: stock description; quantity; Ebay venue quantity; store venue quantity;
3) sales: sale date; stock description; quantity; venue

My process would be to enter purchase data, and have the stock description field be the field that allows me to track inventory. So the idea is that I enter data in the 'stock description' field of the purchases table form, and have that same data appear in the inventory table/form.

What is the best way to have 'stock description' data 'populate the inventory table, in such a way that when I'm done entering data in the purchases form, when I go to the inventory form, I can see the same stock description and quantities I just entered, and assign quantities to the two different sales venues.

Caveat: I can have two or more different purchases having the same stock description. So I would need a 'sum query' to show the sum of all the quantities of a given stock description purchased to date.

Any help would be appreciated.

Marcello

 

[Steve R.]

Read up on normalization.

I did a Google search and here are some data models: Sample Relational Databases. I didn't see one for an inventory system.

Here is one template from Microsoft. Managing Inventory.

I don't know how good or bad these examples are, but they are a start.

 

[jdraw]

Totally agree that you should do some reading on Normalization - it is key to database design.
There are a series of Inventory models at
http://www.databaseanswers.org/data_models/index.htm

Go to data models and look under topic 64 for specific models.
Pay attention to the Business Rules that apply to the models.
Good luck.