Help with design of stock control database (1 Viewer)

[Chumpalot]

Hi,

I've been tasked with designing a database to look after our stock of alcohol. There will be stock being used (consumed) and stock being ordered and replenished.

The old data will be coming from an existing Sharepoint/Sql solution and is in one huge table. I'd like to split this up to cut down on the number of fields and just make things a little neater etc. I was thinking about something like this:

tbl_product - contains the different products, they were sourced from and their prices
tbl_inventory - the current stock levels of the items, how many are booked out for functions, how many are coming in (allows us to do some sums later on down the line if necessary). I was thinking of having a field for 'initial value'. This will be the starting value and the stock amount as of now. From there anything else is just an addition or deletion from the stock number. Thoughts?
tbl_consumed - a table showing products as they're used. (5 wines being used = a new line in this table)
tbl_suppliers - showing supplies
tbl_grade - showing wine grade
etc etc

I have comparatively little Access knowledge. Would this be the best way to go about designing the database? I realise I have given very little in the way of field names etc but hopefully this gives you guys some idea of what I'm trying to achieve.

There'll be 4 main areas of the database:

Products > view all products / check products that may need re-ordering soon / Create new products
Consumption > Checking stock out for functions
Orders > Stock coming in / Suppliers maintenance
Reporting > Custom reporting

How does this all look?

Thanks very much for any help anyone can provide. I really appreciate it.

Cheers
David

 

[jdraw]

Here is a link to an application re Stock Control.
You may get some ideas from this free youtube video.


You may also get some insight from this article by Allen Browne.

Good luck with your project.

 

[plog]

Looks well thought out and pretty good. One main tweak--keep your inventory transactions in 1 table and then create a new table to define your transactions.

TransactionTypes
ttype_ID, autonumber, primary key
ttype_Desc, text, describes the type of transaction (Purchase, Check Out, Check In, etc.)
ttype_Value, number, will be 1 or -1 will be used to determine if adding to inventory or subtracting

StockTransactions
trans_ID, autonumber, primary key
trans_Date, date, date when transaction occured
ID_Product, number, foreign key to Products table
ID_TransType, number, foreign key to TransactionTypes table
trans_Qty, number, quantity of product transacted

When you do that, your current inventory (or inventory on any date in the past) can be simply calculated by adding all the transactions total values together (trans_Qty * ttype_Value).

To start this process I would dump the huge table you currently have into Excel, start designing and structuring your tables and as you do, make sure every field in the spreadsheet is accounted for in your new table structure.

 

[Chumpalot]

Thank you both for your advice.

A quick question. Is it absolutely necessary to create Primary IDs for each of my tables? I can understand why I'd want a primary key in the main stock table so as to avoid duplicates. But what about in and what essentially are lookup tables?

For example:

tbl_stock will be looking at tbl_grade & tbl_group & tbl_winetype as lookup fields.

In tbl_grade I have the following:

[Grade]
A1
A
B
C
D
U

Do I need a primary key field along with this field? Or can I just make this field [Type] the primary key itself?

Thanks very much.
David

 

[jdraw]

Much of relational database is based on these key points by Codd.
I would use a separate PK, but some would not.
https://en.wikipedia.org/wiki/Codd's_12_rules

Here is a list of RESERVED WORDS in Access.

 

[Chumpalot]

Thanks again. I'm still in the design process at the moment. I'm just trying to get it all down on paper so I have it logically in my mind.

 

[The_Doc_Man]

Short answer on PKs - You NEED a PK on ANY table that is going to be on the "1" side of any "1-to-many" or "many-to-1" relationship. You cannot establish the formal relationship without it. Whether the key is natural, synthetic, or surrogate (you can look up those terms by using the SEARCH function in the forum's ribbon) is less important than that Access simply cannot establish a relationship without a PK.

Why do you WANT to establish a relationship? Because, while sometimes Access can appear to be dumber than a box of rocks, having formally established relationships makes the query wizards and form wizards and report wizards work MUCH better.

 

[jdraw]

You may get some ideas from this data model.

 

[Chumpalot]

Hi guys, a little advice if you would be so kind.

As previously mentioned I'm going to record consumption in tbl_consumption. Here's how I see it working:

A new event is arranged. 5 bottle of wine will be used. New Consumption form is opened, the following data is recorded:

- ConsumptionID (PK, hidden from view)
- ProductName (Lookup to tbl_winestock.ProductName) ***
- Customer (free text - will develop this later)
- Quantity
- Username (pulled from login)
- Date of function

The problem is, there are just over 1000 individual products. I need some way of sorting them or filtering them down to avoid using a combo box with all these entries. What would you guys suggest? A textbox filter showing the results in a datasheet perhaps?

Also, I need to be able to add multiple lines of consumption (different products etc). Is there an easy way to do this in Access? Perhaps a tutorial I could follow?

Thanks again for any help.
David

 

[Minty]

I would use a combo - a 1000 products isn't that onerous to start typing into - I have lists of customers that are into the 5000 + numbers and a combo works fine.

Also as previously mentioned you should record both in and out stock movements in one table not 2 or more separate ones. This should be a stockMovement, not just consumption.

Each line of use would be one record in your movement table.

 

[jdraw]

chumpalot,

I think it would be useful to you, and helpful to readers, if you would write a description of the task at hand in plain English. You mention

designing a database to look after our stock of alcohol. There will be stock being used (consumed) and stock being ordered and replenished.

and you also mention Events. Just how these fit together in your set up is important to those offering assistance.

See this free Customers/Orders data model from Barry Williams' site. It is generic and may not meet your needs --it isn't intended to -- but it should give you a starting point for ideas.

Here is another Customers and Services

You can drop, change,add the things important to you.

As Minty advised, use a transaction approach to "current stock level". Stock amount coming in is a positive (+) transaction; stock amount sold/consumed/used is a negative(-) transaction.

So at any time (for a given product)

current stock amount = Last physical stock taking count + sum(positive transactions) - sum(negative transactions).

Also, see this article from Allen Browne re Stock / Inventory Quantity on Hand

Good luck with your project.

 

[Chumpalot]

Thank you all for the help so far. I have a lot to read up on. It's quite difficult trying to muddle through this when my knowledge isn't up to scratch. Sometimes I feel I've bitten off more than I can chew but I think I'll get there.

chumpalot,

I think it would be useful to you, and helpful to readers, if you would write a description of the task at hand in plain English.

This database is purely for stock control. We'll be recording movement of stock in and out. Events aren't being recorded (thankfully) so any reference to this will simply be referring to a free text field for the time being.

Currently I have tables for consumption and for orders (out and in respectively). I will look at merging these. I made separate tables purely from getting ideas from the Northwind Inventory control example.

I'll take a look at your links for the time being. Thank you for posting those.

Regards
David

 

[Chumpalot]

I have become somewhat stuck.

I understand how to record items as single transactions. I have a form built off of the tbl_transactions table. It works fine. However I am completely at a loss when it comes to recording MULTIPLE lines of products in a single transaction.

In my mind I think I have completely confused myself. At the moment I have a form (as above). I made a continuous form and had this as a subform within the main form to allow me to select multiple lines of products and quantities. Is this the way I should be performing this action?

As my tbl_transaction table populates there is no way of going back and seeing what transaction pertained to a specific order (make sense?). Therefore, do I need another table for orders as well? If so, what fields would come under this table?

I am out of my comfort zone here and whilst it's a very good learning experience I've hit a rather large stumbling block here. Please see the attached.

Thank you so much for any help you can provide.
Dave

 

[plog]

I think it would be useful to you, and helpful to readers, if you would write a description of the task at hand in plain English.

You didn't address jdraw's request in a sufficient manner:

This database...free text field...Currently I have tables... I made separate tables

No database jargon in your explanation. Tell us about the business, not about the leap you are trying to make from the business to a database. Pretend its career day for a bunch of 12 year olds. Explain to them what it is you do. Simple english, no database jargon. Tell us the issues you are having about stock management (again not database issues, the business issues).

Also, in the database you posted, tbl_transactions isn't in your Relationship view. I see errors in the relationship view (lookup tables, multiple paths between tables, etc.). But the main concern here is your transaction table. Fit that in and repost.

 

[jdraw]

Dave,

I don't know all your details but I mocked up a model(attached) that may be useful. I would recommend you do not use lookup fields in tables.

Start with a description of the task at hand. Identify major business rules.

I always create a model, even if only on paper. Draw boxes for tables, put PK and other info in the box. Create some scenarios based on your requirements. Mock up some test data. Run your scenarios against the model using your test data.

See this for more info "stump the model".

Good luck. Hope this is useful.

 

[Chumpalot]

You didn't address jdraw's request in a sufficient manner:



No database jargon in your explanation. Tell us about the business, not about the leap you are trying to make from the business to a database. Pretend its career day for a bunch of 12 year olds. Explain to them what it is you do. Simple english, no database jargon. Tell us the issues you are having about stock management (again not database issues, the business issues).

Also, in the database you posted, tbl_transactions isn't in your Relationship view. I see errors in the relationship view (lookup tables, multiple paths between tables, etc.). But the main concern here is your transaction table. Fit that in and repost.

I apologise. I am indeed trying to run (sprint in fact) before I can crawl and it's getting me into all sorts of bother.

The application is going to be used to record all the movements (purchases, consumption, breakages etc) of alcohol of various types. We have wines, brandy, spirits used for functions and each time alcohol is used it'll be recorded on the database. I'd like the database to be able to provide POs too. These don't have to be too complex as the actual purchasing is performed using another application anyway. Along with consumption (stock out) we have the intake of new products too (purchasing).

Currently I have a spreadsheet with a little over 1000 unique products. Some of these are incredibly old and I'd like to have the option of discontinuing these items so that they're effectively filed away somewhere.

The main aims of the database are:

- Capture an initial 'stock take' of the current stock levels that are held.
- Be able to record stock being consumed
- Be able to record new stock being bought
- Be able to effectively report

I managed to find and download a simple Access purchase order template and have adapted this into what I have currently. It works which has given me some hope. I'm trying to learn as I develop and it's proving quite taxing. Again apologies.


Thanks again for the help
David

 

[The_Doc_Man]

I'll answer another question in passing for which I didn't see a clear answer in the thread, though if it were deep in a paragraph I might have missed it.

This has to do with your question on grades A1, A, B, C, etc. etc.

If your grade table is limited to two characters, then a synthetic key (e.g. autonumber) would require 4 bytes (=LONG) - whereas you won't have enough characters to exceed 2 bytes (as long as you don't have grade ZZZ or something like that.) Here, the "natural" key ("A1", "A", "B", "C" etc) is perfectly fine and takes up only 2 bytes.

As long as this encoding method remains unique (I.e. "A" never stands for any other grade than "A" and "A1" is also unequivocal) then there is no barrier to using the natural key. You save two bytes for every graded entry and for every grade in the grade lookup table. Just as important, the comparisons against grade keys are two-byte (same size as INTEGER) comparisons, which is a short instruction in the hardware.

Now, there are theorists out there who MIGHT object to me bringing up the hardware and size points - but here is what I am saying: There is no case where you are EXCEEDING the PK size that would be incurred if you used a synthetic key. There is no awkwardness about using the natural keys.

If your grades sometimes were "AAAAA" then that would be 5 bytes - in which case the synthetic or surrogate key would be shorter and more efficient. But you aren't even close to that, so there is no technical reason to NOT use the natural key.

The same question comes up all the time in state abbreviations for USA addresses. The abbreviations are always 2-character text fields. There is no reason to use a larger key for state names because CA, LA, KA, AK, NY, NJ, etc. all work perfectly well.