Advice on Table Structure Please ?

[spazzer]

Hi All,
I am now going to try and create a database that will keep a check on stock levels in an emergency cupboard we have, I would like to be able to record who takes any stock from the cupboard i.e Name and Pay Number and item taken and whether it was returned or not with dates associated with it.
As stock levels become lower I would like to be able to send an automatic e-mail to my boss to advise of the low stock level and she will then order more supplies, can anyone advise on table structure etc for this as Im new to all of this !
Many Thanks,
Nick C

 

[Atomic Shrimp]

OK, first, you're going to need a table to record the identities of all your items (their description, supplier perhaps, cost, etc.

If the items are big and/or you want to track them individually (for example, if they're chainsaws, rather than boxes of paper clips), then you'll probably need a table with one row for each item (referring back to the item identities table for descriptions, etc), but detailing date acquired, etc.
If they are just boxes of paperclips, then there's no need to track them at individual box level - they're all the same.

Then you probably want a table describing the people who will be taking/returning the items.

Next, you'll need some way of recording what they've taken or returned - a transactions table - for this one, there may only be a few native data fields such as date and quantity - the rest of the fields would just be references (foreign keys) to the ID of the item and the ID of the person.

Once you've got all that, you could build some forms around it - forms to browse/edit the items and people tables, and a form to enter a transaction line in the transactions table. Enter a transaction with a positive qty when you buy in an item, or when someone returns an item, add a transaction line with a negative qty when someone borrows or takes one.

And your stock of any item would then be the sum of the transactions for that item. add in a min stock level to your items table and you can query it against the sum of the transactions table, yielding a bunch of results for items that may need replenishing.