Help please on Table Design for Simple Inventory DB

[arashii]

Hi, I was asked to help create a very simple Access DB for a small restaurant to help them keep track of inventory.

The only things that the owner wants to keep track of are:
1. Item Catalog
2. Date and Quantity of Item IN or OUT
3. Current # of Items

My problem here is that there are 5 Item categories, each with a relatively lost list of Items under each one.
1. Drinks
2. Ice Cream
3. Kitchen Supplies
4. Grocery
5. Etc.

Will it be advisable for me to create an Item Catalog TABLE for each of the category instead of piling all the Items into just one Item Catalog TABLE?

The Designs I’m currently considering are:

A.)

TABLE SET 1 : Catalog of Items
Fields (ID, Name)
• TABLE Drinks
• TABLE Ice Cream
• TABLE Kitchen Supplies
• TABLE Grocery
• TABLE ETC
---------------------------------------------------
TABLE SET 2 : Movement IN/OUT of Items
Fields (Movement ID, Name, Date, IN-Amount, OUT-Amount)
• TABLE Drinks
• TABLE Ice Cream
• TABLE Kitchen Supplies
• TABLE Grocery
• TABLE ETC


B.)
TABLE 1: Catalog of Items
Fields (ID, Category, Name)

TABLE 2: Movement IN/OUT of Items
Fields (Movement ID, Name, Category, Date, IN-Amount, OUT-Amount)

I’m really more inclined to choice A because I just re-learned Access last month and will be creating my first serious database just now. I don’t want to choose a more complex table structure (choice B) that might cause problems with the DB later (as it will really be used to keep track of real-life inventory and might screw up their operations if it goes haywire). Is Design A viable? Or flawed?

Another question I have is with the structure of the Movement – IN/OUT table. Is this workable? My thought is just to let the user enter something like:

Drink [date] [IN] [OUT]
Coke [date] [2] [0]
Coke [date] [0] [1]

In the database, then compute current Quantity of the Item using a query (Sum[IN] – Sum[OUT]) . Will this work? I’m a bit apprehensive with this because I’m thinking I should have another TABLE that will store the actual inventory Quantity instead of the Quantity just being computed through query. But doing another TABLE for Quantity does complicate stuff a lot since it will have to determine which Quantity value gets added to or subtracted from based on many criteria. Will it be OK just to use the query to compute Quantity left?

Thank you so much for any help on this!

 

[RuralGuy]

Maybe this link will be helpful.

 

[arashii]

Hi, thanks for the link . Now I know that my approach in computing for instead of storing Quantity left is not wrong. For the Stocktake Quantity, I'm not sure I can do that since I don't know how to code in VB. Can I just ask the user to sort of input the Actual Quantity left (by doing stocktake) by inputting an IN/OUT value in the Movement table that will equate the calculated Quantity to the Actual Quantity left? ~_~

About creating a different TABLE for each Item Category, will that be workable as well?

 

[neileg]

Hi, thanks for the link . Now I know that my approach in computing for instead of storing Quantity left is not wrong. For the Stocktake Quantity, I'm not sure I can do that since I don't know how to code in VB. Can I just ask the user to sort of input the Actual Quantity left (by doing stocktake) by inputting an IN/OUT value in the Movement table that will equate the calculated Quantity to the Actual Quantity left? ~_~

Yes, that's the most usual way of doing this.

About creating a different TABLE for each Item Category, will that be workable as well?

No. One table unless you mean something quite different from what I think you mean.

 

[arashii]

Hi Neil,

I meant creating a structure like choice A in my first post.

Instead of a TABLE formatted like:
[ID] [Category] [ItemName]

I'm thinking TABLES like:
Category 1: [ID] [ItemName]
Category 2: [ID] [ItemName]
etc.

I'm having trouble putting all the Categories + Items in only one table because it's too long and will entail more complex record entry and querying.

Will doing the multi-table Item Catalog really be a horrible database design? Can I know what problems I might have to face doing this? The database I need to create is simple enough and I don't think it will need that much data or be updated with a lot of new Item Catalog records.

Thank you!

 

[neileg]

I'm thinking TABLES like:
Category 1: [ID] [ItemName]
Category 2: [ID] [ItemName]
etc.

Ok, now I understand. That's the wrong way to do it.

I'm having trouble putting all the Categories + Items in only one table because it's too long and will entail more complex record entry and querying.

Eh? The length of the table is immaterial. Access can cope with millions of records. Users should not even see the tables, they see forms and reports.

Will doing the multi-table Item Catalog really be a horrible database design? Can I know what problems I might have to face doing this? The database I need to create is simple enough and I don't think it will need that much data or be updated with a lot of new Item Catalog records.

Thank you!

Basically, wrong is wrong. When the user comes back and says 'Oh, I forgot, I need another category' or 'I want to combine these two categories' you'll realise the folly of the multi table approach. You need to read up on normalisation.

 

[arashii]

Hi, thanks. The Access course I took didn't really delve into normalization, more of how to create Access forms and controls. Kind of hard to conceptualize how to normalize if the DB doesn't have any data yet (actually, isn't created yet ). But I do get the examples on the normalization readings I looked up.

Which brings me to another question: should I create a separate Category TABLE for my DB? Coz if my Catalog table currently is:

[ID] [Category] [ItemName]
[1] [Drinks] [Coke]
[2] [Drinks] [Sprite]

then the Category field will contain repeating data.

But if I put in a Category table like so:

[ID] [Category]
[1] [Drinks]
[2] [Grocery]

Then my Catalog table will only change like so:
[ID] [Category] [ItemName]
[1] [1] [Coke]
[2] [1] [Sprite]

Would it have achieved anything?

Thank you for the help neil.

 

[neileg]

Would it have achieved anything?

Yup. Firstly, 1 takes less space than Drinks so your db is more efficient. Second, if you want to change your category from Drinks to Soda, you only change one record.

 

[arashii]

Gah, I never saw that far , esp. about changing the category name thing.

So, taking all that in, will this design here already be an OK design to build on? I'm not certain if the relationship should look like that (seems a bit circular) .

 

[neileg]

1) I don't see the need to store Category in tbl_Inventory. Category relates to the item, not the movements.
2) If you replace ItemIN and ItemOUT with a single field, using +ve and -ve values, the current inventory level is simply the sum of that field.
3) Don't use punctuation in your object names other that the underscore. Thus Date_IN/OUT would become Date_INOUT.

I've never had a course in Access so I've learned everything on the run!

 

[arashii]

Hi Neil,

I put the Category field in tbl_Inventory because the main form the users will be using to encode IN/OUT movement is based on that form. I have two comboboxes there that filter the ItemID based on what Category is selected in the 1st combobox. If I only include ItemID, the list that they choose from will be pretty long (200+ choices).

Would this cause a flaw in the design?

About storing Movement INOUT, I did think about doing just one table for it. The two options I see are:

1. Ask users to input (-) values in Movement INOUT in the form.
This will use only 1 textbox for Movement IN or OUT. It might be weird for some users, or they might forget to put the (-) when entering supplies OUT and cause the item to be added to inventory instead.

2. Use 2 textboxes for IN and OUT, but make the values go to the same column in the datasheet.
This seems easier for the users, but I have yet to figure out how to segregate the values into (+) and (-) in the datasheet. I have no VB knowledge and I think this will require coding?

That said, will it really be a bad design idea if I stick to the separate IN and OUT? I see your point that it will definitely save up space in the DB and make for less complex computation, but I'm not sure if I can figure out how to make the form add (-) to the value if it comes from the Movement OUT textbox.

Thanks again for all the critiques and ideas!

I just took a 6-lesson course in Access recently, and it's so cool what you can already do with so limited knowledge LOL ~_~; .

 

[neileg]

Hi Neil,

I put the Category field in tbl_Inventory because the main form the users will be using to encode IN/OUT movement is based on that form. I have two comboboxes there that filter the ItemID based on what Category is selected in the 1st combobox. If I only include ItemID, the list that they choose from will be pretty long (200+ choices).

Would this cause a flaw in the design?

But you don't have to store the category in tbl_Inventory in order to do that.

About storing Movement INOUT, I did think about doing just one table for it. The two options I see are:

1. Ask users to input (-) values in Movement INOUT in the form.
This will use only 1 textbox for Movement IN or OUT. It might be weird for some users, or they might forget to put the (-) when entering supplies OUT and cause the item to be added to inventory instead.

2. Use 2 textboxes for IN and OUT, but make the values go to the same column in the datasheet.
This seems easier for the users, but I have yet to figure out how to segregate the values into (+) and (-) in the datasheet. I have no VB knowledge and I think this will require coding?

That said, will it really be a bad design idea if I stick to the separate IN and OUT? I see your point that it will definitely save up space in the DB and make for less complex computation, but I'm not sure if I can figure out how to make the form add (-) to the value if it comes from the Movement OUT textbox.

I'd use option 2. I think I'd use a combo for the user to select the type of transaction (sale, purchase, adjustment, theft, etc) and base the sign of the movement on that. Yes you'll need a snippet of code, but you're going to have to bite the bullet some time!

Thanks again for all the critiques and ideas!

I just took a 6-lesson course in Access recently, and it's so cool what you can already do with so limited knowledge LOL ~_~; .

I've never taken a course at all!

 

[arashii]

But you don't have to store the category in tbl_Inventory in order to do that.

Argh, that's so right. Using the Category as filter doesn't necessarily mean it goes into the table as well ~_~;;; .

I'd use option 2. I think I'd use a combo for the user to select the type of transaction (sale, purchase, adjustment, theft, etc) and base the sign of the movement on that.

This is such a good idea! I'll try to see if I can make Access do the (+) and (-) based on that combobox. But if I really can't, will the separate fields for IN and OUT cause a lot of problems for computations?

Thanks so much!

 

[neileg]

This is such a good idea! I'll try to see if I can make Access do the (+) and (-) based on that combobox.

I would have a field in the transaction type table that would feed the combo box and populate this with either 1 or -1. This would be hidden in the form but you can still reference this field in your code. Multiply the user input by this field to give the appropriate sign.

But if I really can't, will the separate fields for IN and OUT cause a lot of problems for computations?

Why not make life as easy as possible? Although I'm an accountant by profession, I trained as a mathematician. Mathematicians know there's always a easy way to do everything!

Thanks so much!

Everything I know about Access, I learned on these forums. Just passing on the favour.

 

[arashii]

Hmm.. so I create another table that just has

+1
-1

in it?

I'll try that. True there are lots of ways to make things simpler... I only wish I had the skill level to do it.

Lol, before I was trying to create combos to make dropdown DATE entries... utnil someone told me about the built-in Calendar control ^_^; .

 

[neileg]

Hmm.. so I create another table that just has

+1
-1

in it?

No. You have a table with three fields:

tblTransactionType
TransTypeID PK autonumber
TransDecsription eg Sale, Adjustment
TransSign +1 or -1 depending on transaction type.

 

[arashii]

Hi neil,

Thanks for the tips . I tried doing that table but eventually got confused by all the links and computations I had to do. So I guess I'm sticking with the 2 fields for Inventory IN and OUT. I just followed your tip on having a combobox specify if the Movement is IN or OUT, then made the corresponding input text fields visible and invisible accordingly ~_~; . Atleast it will keep the user from making mistakes in inputting the values.

Thanks again for all the help and time .

 

[neileg]

If you're happy, I'm happy.

Most people make the mistake of thinking that inventory is simple - it's not. I think you know that now!