Design Question

[Michael Thomas]

Hi everyone

Not sure if this is the right newsgroup to be posting to for this question,
but I am using Access 2002 to develop a database solution for the company
that I work for. It's basically as stock control system where I need to
keep track of the quantities of products.

Our company is a retail chain, and we have eight shops or locations and a
head office. The tables that I've set up are as follows

- tblProducts with fields ItemBarcode, ItemDescription ,
CostPrice,Selli ngPrice,Supplie rID,QtyLoc1,Qty Loc2,QtyLoc3... QtyLoc8

ItemBarcode is the primary key

The Qty fields represent the quantity of each item at that particular
location.

I then have a table which lists my suppliers and the SupplierID field is the
primary key in this table and is linked to SupplierID in tblProducts.

The problem is that surely I should be able to no have to have the QtyLoc1
through to QtyLoc8 fields? There must be a better way, like creating a
location table with each Location listed? Each location has exactly the same
items so I need to use the ItemBarcode and/or the ItemDescription fields for
each shop and then record quantities of each of the items at each of the
shops.

Please help :-( I know I sound like a noob but prior to me arriving at the
company, they were still using a DOS based back office which can no longer
be used to keep track of 3227 items at 8 different stores with quantities of
each item at each store etc.

ANY help would be appreciated, even if you redirect me to another newsgroup.
Thank you.

Michael Thomas
Stock Controller of The Cock 'n Bull cc
Cape Town
South Africa

[rkc]

"Michael Thomas" <mi*****@cocknb ull.co.za> wrote in message
news:40******** @news1.mweb.co. za...

Not sure if this is the right newsgroup to be posting to for this question, but I am using Access 2002 to develop a database solution for the company
that I work for. It's basically as stock control system where I need to
keep track of the quantities of products.

Our company is a retail chain, and we have eight shops or locations and a
head office. The tables that I've set up are as follows

- tblProducts with fields ItemBarcode, ItemDescription ,
CostPrice,Selli ngPrice,Supplie rID,QtyLoc1,Qty Loc2,QtyLoc3... QtyLoc8

ItemBarcode is the primary key

The Qty fields represent the quantity of each item at that particular
location.

I then have a table which lists my suppliers and the SupplierID field is the primary key in this table and is linked to SupplierID in tblProducts.

The problem is that surely I should be able to no have to have the QtyLoc1
through to QtyLoc8 fields? There must be a better way, like creating a
location table with each Location listed? Each location has exactly the same items so I need to use the ItemBarcode and/or the ItemDescription fields for each shop and then record quantities of each of the items at each of the
shops.

Your instincts are correct. There are several fundamental flaws in the
the Products table as far as design for a relational database is concerned.
The obvious one is the field repeats for each location. An offshoot of this
is that the field actually contains two pieces of information. The
identification of the Location is incorporated into the name of the field.
Data belongs in the table, not as part of the structure. First normal form
dictates that each field contains only one piece of information. The
repeating field is also considered a violation of first noraml form, but on
top of that it makes it necessary to alter the table structure when a new
location is added.

So how do you fix it?
Remove the Location and Quantity information from the Products table.

Where do you put it?
Location belongs in a Locations table that holds location information.
Everything in the Locations table should be about the location and only
about the location.
tblLocations (LocationID*, City, Address, other location specific
information)

Quantity is a bit more complicated. It belongs in a table that relates the
Product to the Location and also holds the Quantity information.
tblProductsLoca tions (LocationID*, ProductID*, Quantity)

How do you know what Supplier supplys a product?
You already have a Supplers table. The suppliers table should be similar to
locations table. All information in the table should be about the supplier.
Suppliers(Suppl ierID*, SupplierName, City, Address, etc...)

As with locations and products, you need a table to relate suppliers to
products.
tblSuppliersPro ducts (SupplierID*, ProductID*)

So starting with:

tblProducts (ItemBarcode*, ItemDescription ,
CostPrice,Selli ngPrice,Supplie rID,
QtyLoc1,QtyLoc2 ,QtyLoc3...QtyL oc8)
You end up with:

Products (ProductID*, ItemBarcode, ItemDescription , CostPrice, SellingPrice)
Note: If a product barcode never changes it would be a perfectly good
primary key. Otherwise adding a never changing field like ProductID
is probably a better choice.

Suppliers (SupplierID*, SupplierName, City, Address, etc...)

Locations (LocationID*, City, Address, etc...)

ProductsSupplie rs (ProductID*, SupplierID*)

ItemsLocations (LocationID*, ProductID*, Quantity)

[Michael Thomas]

Hey all

Thank you for responding.

I understand what you're saying and I agree with it, except for the
ItemsLocations table.

We have 3000 or more items that we sell at each one of the locations so the
quantities are changing all the time.

The ItemsLocations table will end up being huge, wouldn't it? YOu'd have to
have 3000 items typed/pasted into the table each time you wanted to update
quantities for a single location. Am I right?

"rkc" <rk*@yabba.dabb a.do.rochester. rr.bomb> wrote in message
news:cX******** ***********@twi ster.nyroc.rr.c om...

"Michael Thomas" <mi*****@cocknb ull.co.za> wrote in message
news:40******** @news1.mweb.co. za...

Not sure if this is the right newsgroup to be posting to for this question,

but I am using Access 2002 to develop a database solution for the company that I work for. It's basically as stock control system where I need to
keep track of the quantities of products.

Our company is a retail chain, and we have eight shops or locations and a head office. The tables that I've set up are as follows

- tblProducts with fields ItemBarcode, ItemDescription ,
CostPrice,Selli ngPrice,Supplie rID,QtyLoc1,Qty Loc2,QtyLoc3... QtyLoc8

ItemBarcode is the primary key

The Qty fields represent the quantity of each item at that particular
location.

I then have a table which lists my suppliers and the SupplierID field is

the

primary key in this table and is linked to SupplierID in tblProducts.

The problem is that surely I should be able to no have to have the QtyLoc1 through to QtyLoc8 fields? There must be a better way, like creating a
location table with each Location listed? Each location has exactly the

same

items so I need to use the ItemBarcode and/or the ItemDescription fields

for

each shop and then record quantities of each of the items at each of the
shops.

Your instincts are correct. There are several fundamental flaws in the
the Products table as far as design for a relational database is

concerned. The obvious one is the field repeats for each location. An offshoot of this is that the field actually contains two pieces of information. The
identification of the Location is incorporated into the name of the field.
Data belongs in the table, not as part of the structure. First normal form
dictates that each field contains only one piece of information. The
repeating field is also considered a violation of first noraml form, but on top of that it makes it necessary to alter the table structure when a new
location is added.

So how do you fix it?
Remove the Location and Quantity information from the Products table.

Where do you put it?
Location belongs in a Locations table that holds location information.
Everything in the Locations table should be about the location and only
about the location.
tblLocations (LocationID*, City, Address, other location specific
information)

Quantity is a bit more complicated. It belongs in a table that relates the
Product to the Location and also holds the Quantity information.
tblProductsLoca tions (LocationID*, ProductID*, Quantity)

How do you know what Supplier supplys a product?
You already have a Supplers table. The suppliers table should be similar to locations table. All information in the table should be about the supplier. Suppliers(Suppl ierID*, SupplierName, City, Address, etc...)

As with locations and products, you need a table to relate suppliers to
products.
tblSuppliersPro ducts (SupplierID*, ProductID*)

So starting with:

tblProducts (ItemBarcode*, ItemDescription ,
CostPrice,Selli ngPrice,Supplie rID,
QtyLoc1,QtyLoc2 ,QtyLoc3...QtyL oc8)
You end up with:

Products (ProductID*, ItemBarcode, ItemDescription , CostPrice, SellingPrice) Note: If a product barcode never changes it would be a perfectly good
primary key. Otherwise adding a never changing field like ProductID is probably a better choice.

Suppliers (SupplierID*, SupplierName, City, Address, etc...)

Locations (LocationID*, City, Address, etc...)

ProductsSupplie rs (ProductID*, SupplierID*)

ItemsLocations (LocationID*, ProductID*, Quantity)

[Bernard Peek]

In message <40********@new s1.mweb.co.za>, Michael Thomas
<mi*****@cocknb ull.co.za> writes

Hi everyone

Not sure if this is the right newsgroup to be posting to for this question,
but I am using Access 2002 to develop a database solution for the company
that I work for. It's basically as stock control system where I need to
keep track of the quantities of products.

Our company is a retail chain, and we have eight shops or locations and a
head office. The tables that I've set up are as follows

- tblProducts with fields ItemBarcode, ItemDescription ,
CostPrice,Sell ingPrice,Suppli erID,QtyLoc1,Qt yLoc2,QtyLoc3.. .QtyLoc8
I see a problem there. What happens if you buy two batches of a product
at different times and at different prices?

ItemBarcode is the primary key

The Qty fields represent the quantity of each item at that particular
location.

I then have a table which lists my suppliers and the SupplierID field is the
primary key in this table and is linked to SupplierID in tblProducts.

The problem is that surely I should be able to no have to have the QtyLoc1
through to QtyLoc8 fields? There must be a better way, like creating a
location table with each Location listed? Each location has exactly the same
items so I need to use the ItemBarcode and/or the ItemDescription fields for
each shop and then record quantities of each of the items at each of the
shops.

Please help :-( I know I sound like a noob but prior to me arriving at the
company, they were still using a DOS based back office which can no longer
be used to keep track of 3227 items at 8 different stores with quantities of
each item at each store etc.

ANY help would be appreciated, even if you redirect me to another newsgroup.

I'm a database purist so I always start database design with an Entity
Relationship Diagram that shows the underlying structure of the data.
Each entity is one type of "thing" that your database needs to record.
So "Location" is a type of thing and that should appear on the diagram
even if you don't have a location table.

Once you have all of the entities on the diagram you need to establish
their relationships, for instance there is a relationship between
supplier and the batch of goods they supply, and another one between the
batch entity and the products contained in the batch.

Once you have all of the entities and relationships diagrammed you can
start to turn the entities into tables in your physical database design.

You could combine the inventory and location entities by having eight
separate quantity fields in the inventory table. It's a valid data
structure. But consider what happens if you get another location. You
need to go back into the inventory table and add a ninth location. You
will probably have to amend all of the programs that use that table. If
you know for sure that there will never be a ninth location then this
isn't something you have to worry about.

A better alternative, as you have obviously realised, is to use a
separate location table. You programs then just report the inventory for
each location in the table, and your program doesn't need to change if
you add a ninth or tenth site.

There is a certain amount of database theory you should study before you
build a complex database. It's not too much and it's better to learn it
now than to have to rebuild your database later.

The main thing to understand is normalisation (or perhaps South Africa
uses the American spelling normalization.) This is the process of taking
the data you have and working out its logical structure. The aim is to
make sure that you only record each item of data once, so that if you
have to correct it you only have to correct it in one place.
Normalisation is what you did when you decided that it was better to
have a separate location table so you obviously have a pretty good idea
of what it involves.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

[Michael Thomas]

Hi

Could you point me to any good online resources for normalisation? Been
searching the web, but can't find much. If there isn't anything decent
online, do you have any book recommendations ?

Thanks,
Michael

"Bernard Peek" <ba*@shrdlu.com > wrote in message
news:bY******** ******@shrdlu.c om...

In message <40********@new s1.mweb.co.za>, Michael Thomas
<mi*****@cocknb ull.co.za> writes

Hi everyone

Not sure if this is the right newsgroup to be posting to for this question,but I am using Access 2002 to develop a database solution for the company
that I work for. It's basically as stock control system where I need to
keep track of the quantities of products.

Our company is a retail chain, and we have eight shops or locations and a
head office. The tables that I've set up are as follows

- tblProducts with fields ItemBarcode, ItemDescription ,
CostPrice,Sell ingPrice,Suppli erID,QtyLoc1,Qt yLoc2,QtyLoc3.. .QtyLoc8
I see a problem there. What happens if you buy two batches of a product
at different times and at different prices?

ItemBarcode is the primary key

The Qty fields represent the quantity of each item at that particular
location.

I then have a table which lists my suppliers and the SupplierID field is theprimary key in this table and is linked to SupplierID in tblProducts.

The problem is that surely I should be able to no have to have the QtyLoc1through to QtyLoc8 fields? There must be a better way, like creating a
location table with each Location listed? Each location has exactly the sameitems so I need to use the ItemBarcode and/or the ItemDescription fields foreach shop and then record quantities of each of the items at each of the
shops.

Please help :-( I know I sound like a noob but prior to me arriving at thecompany, they were still using a DOS based back office which can no longerbe used to keep track of 3227 items at 8 different stores with quantities ofeach item at each store etc.

ANY help would be appreciated, even if you redirect me to another

newsgroup.
I'm a database purist so I always start database design with an Entity
Relationship Diagram that shows the underlying structure of the data.
Each entity is one type of "thing" that your database needs to record.
So "Location" is a type of thing and that should appear on the diagram
even if you don't have a location table.

Once you have all of the entities on the diagram you need to establish
their relationships, for instance there is a relationship between
supplier and the batch of goods they supply, and another one between the
batch entity and the products contained in the batch.

Once you have all of the entities and relationships diagrammed you can
start to turn the entities into tables in your physical database design.

You could combine the inventory and location entities by having eight
separate quantity fields in the inventory table. It's a valid data
structure. But consider what happens if you get another location. You
need to go back into the inventory table and add a ninth location. You
will probably have to amend all of the programs that use that table. If
you know for sure that there will never be a ninth location then this
isn't something you have to worry about.

A better alternative, as you have obviously realised, is to use a
separate location table. You programs then just report the inventory for
each location in the table, and your program doesn't need to change if
you add a ninth or tenth site.

There is a certain amount of database theory you should study before you
build a complex database. It's not too much and it's better to learn it
now than to have to rebuild your database later.

The main thing to understand is normalisation (or perhaps South Africa
uses the American spelling normalization.) This is the process of taking
the data you have and working out its logical structure. The aim is to
make sure that you only record each item of data once, so that if you
have to correct it you only have to correct it in one place.
Normalisation is what you did when you decided that it was better to
have a separate location table so you obviously have a pretty good idea
of what it involves.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

[rkc]

"Michael Thomas" <mi*****@cocknb ull.co.za> wrote in message
news:40******** @news1.mweb.co. za...

Hey all

Thank you for responding.

I understand what you're saying and I agree with it, except for the
ItemsLocations table.

We have 3000 or more items that we sell at each one of the locations so the quantities are changing all the time.

The ItemsLocations table will end up being huge, wouldn't it? YOu'd have to have 3000 items typed/pasted into the table each time you wanted to update
quantities for a single location. Am I right?

It's true that the number of records in the ItemsLocation table could be
number of items * number of stores. In terms of what Access and the Jet
Database Engine can handle efficiently that number is insignificant.

As far as updating quantities, the products and locations are entered only
once. After that the quantity field is simply updated. More records are
added only when more products or locations are added.

As a side note, please don't mistake what I posted as the design for a
working inventory database. What I posted was just an illustration of
how to normalize the tblProducts example you posted. A useful database
would get much more complicated real quickly.

You asked for online normalization references in another post. Here's
one I think is worth looking at.

http://www.guides.sk/reldb_dsgn/index.htm#start

[Michael Thomas]

Hi again

I've taken your advice and changed the structure of my database to the
following...

Products (ItemBarcode*, ItemDescription , CostPrice, SellingPrice, DeptID)

Suppliers (SupplierID*, SupplierName, City, Address, etc...)

Locations (LocationID*, City, Address, etc...)

ProductsSupplie rs (ItemBarcode*, SupplierID*)

ItemsLocations (LocationID*, ItemBarcode*, Quantity)

Departments(Dep tID*, DepartmentName)

Where DeptID is an autonumber list, a department describes a group to which
a product belongs.

The problem I'm still having is that if I have a list of say, for example,
twenty records of products and quantities for one location in my
ItemLocations table, then I want to record quantities for the same twenty
items, but for a different location, I end up having to type out the list of
products/choose them from a list box or combo box individually or copy and
paste them and then type in the new locations name and THEN only get to
typing in the quantities. What if I wanted to get a list of quantites of
each of the twenty products for each location in spreadsheet form where each
row would represent a product and each column would represent a location and
where rows and columns meet would be the quantity of that particular product
at that particular location. How would I get such a query when my
ItemLocations table lists the same items over and over ? ;~~-(

Is there a problem with my design, I can't see how data is redundant. Each
list is only stored once. Or is it a problem with me not knowing how to
construct the query?

Please help, I'm very frustrated. Thank you,

Michael

"rkc" <rk*@yabba.dabb a.do.rochester. rr.bomb> wrote in message
news:I2******** ***********@twi ster.nyroc.rr.c om...

"Michael Thomas" <mi*****@cocknb ull.co.za> wrote in message
news:40******** @news1.mweb.co. za...

Hey all

Thank you for responding.

I understand what you're saying and I agree with it, except for the
ItemsLocations table.

We have 3000 or more items that we sell at each one of the locations so the

quantities are changing all the time.

The ItemsLocations table will end up being huge, wouldn't it? YOu'd have to

have 3000 items typed/pasted into the table each time you wanted to

update quantities for a single location. Am I right?

It's true that the number of records in the ItemsLocation table could be
number of items * number of stores. In terms of what Access and the Jet
Database Engine can handle efficiently that number is insignificant.

As far as updating quantities, the products and locations are entered only
once. After that the quantity field is simply updated. More records are
added only when more products or locations are added.

As a side note, please don't mistake what I posted as the design for a
working inventory database. What I posted was just an illustration of
how to normalize the tblProducts example you posted. A useful database
would get much more complicated real quickly.

You asked for online normalization references in another post. Here's
one I think is worth looking at.

http://www.guides.sk/reldb_dsgn/index.htm#start

[rkc]

"Michael Thomas" <mi*****@cocknb ull.co.za> wrote in message
news:40******** @news1.mweb.co. za...

Hi again

I've taken your advice and changed the structure of my database to the
following...

Products (ItemBarcode*, ItemDescription , CostPrice, SellingPrice, DeptID)

Suppliers (SupplierID*, SupplierName, City, Address, etc...)

Locations (LocationID*, City, Address, etc...)

ProductsSupplie rs (ItemBarcode*, SupplierID*)

ItemsLocations (LocationID*, ItemBarcode*, Quantity)

Departments(Dep tID*, DepartmentName)

Where DeptID is an autonumber list, a department describes a group to which a product belongs.
The problem I'm still having is that if I have a list of say, for example,
twenty records of products and quantities for one location in my
ItemLocations table, then I want to record quantities for the same twenty
items, but for a different location, I end up having to type out the list of products/choose them from a list box or combo box individually or copy and
paste them and then type in the new locations name and THEN only get to
typing in the quantities.
You can run an append query to add the ItemBarcode and LocationID to
the ItemsLocation table. For example the following query will add records
for
all products and the LocationID 1.

INSERT INTO ItemsLocations (LocationID, ItemBarcode )
SELECT 1 , Products.ItemBa rcode
FROM Products;

You can do the same for all LocationID values by substituting the
correct LocationID for the number 1 in the above query.

Then all you have to do is add the Quantity value by hand.
What if I wanted to get a list of quantites of
each of the twenty products for each location in spreadsheet form where each row would represent a product and each column would represent a location and where rows and columns meet would be the quantity of that particular product at that particular location. How would I get such a query when my
ItemLocations table lists the same items over and over ? ;~~-(

Once you get your data entered into the your tables, lookup Crosstab queries
in the help file. A basic crosstab using just the ItemsLocations tables
would
look like the following which was created using the wizard.

TRANSFORM Sum(ItemsLocati ons.Quantity) AS SumOfQuantity
SELECT ItemsLocations. LocationID,
Sum(ItemsLocati ons.Quantity) AS [Total Of Quantity]
FROM ItemsLocations
GROUP BY ItemsLocations. LocationID
PIVOT ItemsLocations. ItemBarcode;

[Bernard Peek]

In message <40********@new s1.mweb.co.za>, Michael Thomas
<mi*****@cocknb ull.co.za> writes

Hi

Could you point me to any good online resources for normalisation? Been
searching the web, but can't find much. If there isn't anything decent
online, do you have any book recommendations ?

I didn't learn from a book and in any case books I read then would be
out of print by now.

The only tangible help I can offer is to point out the
comp.databases. theory newsgroup where there are always people ready to
help.

I've crossposted this response to that newsgroup.


--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

[John Baker]

Michael Thomas wrote:

Could you point me to any good online resources for normalisation? Been
searching the web, but can't find much. If there isn't anything decent
online, do you have any book recommendations ?

O'Reilly has a book on Access called Access Database Design &
Programming, 3rd Edition. I haven't read it, but if you go to this address:

http://www.oreilly.com/catalog/accessdata3/

There is a description and also a link to a sample chapter. The sample
chapter happens to be on database design and normalization. So, you
could read the sample chapter to get some info on what you're looking
for, and if you happen to find it helpful and well written then you know
what book to buy.