KevinP & All Database Experts, You're Wanted at the Front Desk

**Kevin P** · 04 August 2002, 19:27 Sunday

Sounds like a good start there Jeff. But without more information (such as what fields are in each table), I wouldn't be able to offer additional suggestions as to how the data is organized, normalized, etc.

Just one question though, what is the "Finish" table? Is that the inventory table? You should probably call it something with "inventory" in the name.

In case you're not fully familiar with the "rules" of normalization, here they are in a nutshell (I'm borrowing from one of my Access books for this):

1. Each column (field) in each row of each table must contain only one value. In other words, stringing multiple values together into a single column "1, 2, 3..." would violate 1st order normalization. Also, columns should not be repeated. For example, a table storing sales totals for each month of the year could be (badly) designed like this:

Year, JanSales, FebSales, MarSales, ... DecSales

In that table you'd have 12 sales totals columns, and one row for each year. Imagine if you had to generate a report listing total sales for the year with that table. It would be a mess! A better design is:

Year, Month, TotalSales

This table would have Year and Month as the primary key, and one row for each month. Then you can easily query any month's total sales, and use an aggregate query to get sales totals.

2. All non-key columns must be fully dependent on the primary key. In other words, a table must store data relating to only one "thing". The product table, for example, with a primary key of ProductID must only store data relating directly to a product. If you have, let's say, order information in that table, that violates 2nd order normalization. Order information belongs in a separate table from product information.

3. All non-key columns must be mutually independent. In other words, you should be able to change the value of one column without it affecting the values in other columns. An example of a no-no is storing Quantity, Per-Item Cost, and Total Cost in a table. Total Cost is dependent on Quantity and Per-Item Cost, so you are better off calculating Total Cost on the fly instead of storing it in the table.

There are 4th and 5th order normalization rules as well, but they're more exotic and probably more trouble than it's worth. Sometimes you'll have to "break" normalization rules in some instances to get the data the way you need it. For example, "copying" product data into the Inventory table technically violates normalization, but is the best way to accomplish your specific needs (handling price changes, etc.)

Hope this helps without hopelessly confusing you! :? 8O

Official Computer Geek and Techno-Wiz Guru of HTGuide - Visit Tower of Power
My HT Site

**Jeff Kowerchuk** · 05 August 2002, 00:18 Monday

Kevin;

Thanks for your time.

I think I've done it right:

Table: Categories
Fields: 2
Field1: CategoryID (autonumber, primary key)
Field2: CategoryName

Table: Brands
Fields: 2
Field1: BrandID (autonumber, primary key)
Field2: BrandName

Table: Products
Fields: 2
Field1: ProductID (autonumber, primary key)
Field2: ProductName

Table: Finish
Fields: 2
Field1: FinishID (autonumber, primary key)
Field2: FinishName

(Incidentally, 'Finish' refers to the finish of a product. The same product can be available in different finishes.)

Given this structure, I need a Category-Brand junction table, as well as a Brand-Product junction and a Product-Finish junction.

Am I headed in the right direction?

Then, the actual 'Inventory' table would be as such:

Category, Brand, Product, Finish

and would simply refer to the relevant tables using the primary key. So, for example, one record may look like:

1, 3, 74, 2

I think this is what is meant by normalization - though it makes the inventory table kinda confusing just to look at, since its all numbers - of course we'd never be looking directly at it anyway.

Also, there will be more fields in the Inventory table, like pricing of course.

Thanks again.

/Jeff

**Kevin P** · 05 August 2002, 08:34 Monday

That looks pretty darned normalized to me!

That structure should work out for you.

You might want to set up relationships between the inventory table and the junction tables, to enforce valid combinations. For example, a relationship between the Category-Brand junction table and the corresponding fields in the inventory table will ensure that you don't have a brand that doesn't go with a category in the Products table.

Another way to structure your data would be like this:

Categories, Brands, and Finish the way they are now, but with Products as:

Table: Products
Fields: 5 (or more)
Field1: ProductID (autonumber, primary key)
Field2: CategoryID (relates to Category table)
Field3: BrandID (relates to Brand table)
Field4: FinishID (relates to Finish table)
Field5: ProductName
(plus any other fields relating to the product, such as description, unit price, weight etc.)

Then your Inventory table would be:

InventoryID (primary key)
ProductID (relates to Product)
(plus other fields, such as quantity, price, etc.)

This adds some hierarchy to your database structure. In any case, regardless of how you design the database, if it's normalized properly you can still create a query (view) that joins all this stuff together so you can view the data in a more human-friendly format (Category Name, Brand Name, Finish Name, Product Name, etc.) without having to look at those ID fields.

KJP

Official Computer Geek and Techno-Wiz Guru of HTGuide - Visit Tower of Power
My HT Site

**Lex** · 05 August 2002, 18:35 Monday

The thing that I will add to this, is, think about it in terms of how many of each different combination could their be?

It might make more sense to narrow the number of tables, if increasing the number of rows in a table will not be that large. In short, it might take less time to access 2 tables, sifting through a few more rows with MORE KEYS, than to access 4 different tables for each set of data accessed.

Food for thought. But I am not a DB designer. My task is to use them once defined. I have had input into a few tables, but not entire systems.

Lex

**Jeff Kowerchuk** · 08 August 2002, 15:25 Thursday

Kevin & Lex:

Thanks for the input guys!

I think I see where you're headed with the 'Products' table - it seems to make sense to me, and it might help me with the structure. While I understand the concept of normalization, I have to admit having all these small tables just makes things seem very complex.

I have another question. There are products in our inventory that have a serial number. This is how we track these items. The idea was, at the POS, the user simply enters the serial number to add the item - seemed like a slick solution, since we'd have to verify the serial number at some point. By entering the serial number, the program searches for that product in the inventory and yanks it out.

The thing is, we also have a lot of products that have no serial number, but instead are tracked by quantity. For example, we might have 300 Widgets. Obviously this can't be hunted down by serial #, so I should I create unique product IDs for these kinds of items? What would be the best way to implement them in terms of the tables. Should items without serial numbers be kept in a seperate inventory table?

Also, what about things that aren't really in inventory? For example, we might assemble things for customers and have a set charge. Where should that be entered?

Thanks guys, as always. I'd be lost without you. :LOL:

/Jeff

**Kevin P** · 08 August 2002, 15:52 Thursday

If you have small quantities of products, the serial number as the key would work ok (say, big screen TVs or motorcycles where you only stock a few of each one). But if you have 300 widgets you shouldn't need to enter each one individually. A better solution is to have one row for each unique product, with a quantity on hand in the inventory. The key could be the UPC code, or some unique value for things like assembly charges. Then when you sell a widget, you decrease the quantity by one. When you reorder and get 300 new widgets, you add 300 to the quantity.

The advantage of using the UPC code as a key is you can scan it with a bar code scanner, if it comes to that. And by tracking quantities, you can generate reports to determine how many widgets you sell per month, or to determine when you get low on doohickeys and need to order more.

As for the normalization, don't lose sleep over it. 100% normalized isn't always the way to go. If something is one-to-one with say a product then keep it in the product table. If something is one-to-many to a product (say, serial numbers or sales details), make it a child table.

KJP

Official Computer Geek and Techno-Wiz Guru of HTGuide - Visit Tower of Power
My HT Site

**Jeff Kowerchuk** · 12 August 2002, 13:42 Monday

Can two VB apps access one Access file at the same time?

I ask because as I work this out, I'm thinking of creating a couple different VB apps, as opposed to one big main. The main purpose (and the most frequent use) for this program will be inventory and POS. But there are other more mundane administration things like adding new Categories, Brands etc., which I thought could go in a seperate app.

We also need the ability to do a search through old invoices. Invoices will be kept in a seperate table. But we might want to do a search with the POS system running. This is where I was wondering if it makes more sense to have seperate Access files for things.

/Jeff

**Gordon Moore** · 13 August 2002, 11:51 Tuesday

Can two VB apps access one Access file at the same time?

Sure, The whole point of a database is to centralize your data. Adding locking to your tables or rows is a good way of increasing concurrent access. The database will automatically introduce a basic level of locking by default. Some transactions implicitly lock a table anyways (like UPDATES).

Pickup "LAN TIMES Guide to SQL" it is probably the best reference book you'll ever have. Readup on the the SHARED and EXCLUSIVE Locking. You should also consider SavePoints for your commits and rollbacks

"A RONSTER!"

KevinP & All Database Experts, You're Wanted at the Front Desk

KevinP & All Database Experts, You're Wanted at the Front Desk

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment