Inventory transaction table with colors & sizes

[mminer]

I

[Barbecue]

By having multiple fields to store information about the different sizes in one record, you have created the functional equivalent of repeating fields, which is usually a bad idea in a case like this.

There are several problems with doing it this way: First, for a product that is not available in all seven sizes, you need to identify that those fields are unused for that product. Second, if something should change in your product line, such as adding a new size or changing a calculation based on a size, you'll have to make changes to the fields themselves to account for it. Changing a calculation means changing it seven times instead of once, and that's a good way to introduce errors. Third and probably most important, this way of storing data is not efficient, and you're concerned about performance. If someone orders only one size of an item, the other six sizes are unused, wasted space on the screen and in the table.

A more normalized approach to this would be to have a "Products" table, where each product is a particular design in a particular size. You already have a table for "Categories", simply extend that idea to having tables for sizes and designs as well.

The sizes and designs can be looked up from their own separate tables. Adding a new size then becomes just adding a new record instead of changing the design of the database, and there will be no empty, unused fields. If an item isn't available in a particular size, there's no record for it, so no chance of it being selected by mistake.

This means your system will have more tables, but that's not a bad thing. Good relational design generally results in having lots of tables, with a small number of fields in each table.

I think you'll find this will make your inventory calculations easier as well, since each inventory item (product) will have just one quantity field to keep track of. You can get various inventory total reports by size, by design, or whatever simply by finding and sorting your records.

This will give you a more flexible system that will be easier to maintain and probably smaller and more efficient as well.

As far as how to handle the inventory calculations, there are advantages to doing batch processing versus live calculations. The best approach will depend on the logistics of your order processing.

You might want to do a combination, doing on-the-fly calculations of inventory for sales reference during the day, but not committing the totals until an end-of-day closing.

[mminer]

Thanks for your informative reply, Barbecue. It's very helpful.

You've probably sold me but let me explain why I've hesitated using the design and the size combination as my order line item instead a quasi "repeating" field showing sizes in each design.

Your suggestion requires the person entering data to enter a design ID and a size ID for each item ordered. If an average of 4 sizes are chosen in each design, about 8 times more IDs need to be keyed in for each design ordered (each design and each size 4 times instead of a design ID once). Do you have a suggestion as to how to speed data entry?

The other problem I foresee is how do I get the layout to fit nicely with a line for each design? Invoices wouldn't be very readable and may use 2-3 pages each if I have to use one line to list each design and size instead of just each design.

.... can you close the deal? Thanks!

[Barbecue]

I've been away for a few days, so I just saw your response...

With my suggestion (which is just a more relational design), you would not need to enter both a design and size ID for each ordered item.

Each product's size and design information is entered one time only into the products table. From that point on, you just select or enter the Product id and the other information comes with it.

The Product ID must be unique, but can be arbitrary. So for convenience for the data entry people, you could use some combination of letters and numbers that helps the user remember what item it represents.

For example, if I want to keep track of screws, I might use SWS for stainless steel wood screw, GWS for Galvanized wood screw, etc. For each type, there are multiple sizes, ranging from 1/4 inch to 1 1/2 inch, which can be indicated with a number. So a 1 inch stainless steel screw would be SWS100, a three-quarter inch galvanized wood screw could be GWS034, a half-inch machine screw might be SMS012, and so forth. I have attached an old demo database that I have updated to FM7. It illustrates the concept.

With respect to the invoice, if you really want to group items together on a single line this way, you can use a calculated field or a script to create a formatted line on-the-fly. But I think you should reconsider. Using separate lines for each item is standard practice when invoicing. Althought the invoice might be longer, it's going to be much easier to understand, especially if you. Take a look at invoices sent by other vendors, almost all will break things down this way.

screws.zip

[Kathysl2]

Barbeque,

In your attached example, how would you relate onhandcount to line items so that it actually subtracted items from the inventory when something was bought?

Kathy

[Kathysl2]

Barbeque,

In your attached example, how would you relate onhandcount to line items so that it actually subtracted items from the inventory when something was bought?

Kathy

[Kathysl2]

Barbeque,

In your attached example, how would you relate onhandcount to line items so that it actually subtracted items from the inventory when something was bought?

Kathy