Your Opinion on Design Question

I'll try to describe the situation pretty thoroughly and then I have a pretty
specific question. For an Inventory database, we are trying to keep track of
2 pieces of information. One piece is the quantity of each item (each item
is defined by an SKU number). The other piece is the location of each item.
Items can be and will be in more than 1 warehouse. There are 4 warehouses
and each warehouse has hundreds of locations. One table is the Product Table
which defines Vendors and other pieces of information. Now I want either of
these two configurations:

A) Multiple Tables
1) Locations Table which contains a SKU and information about which
warehouse or warehouses hold the item
2) Warehouse Tables - a separate table for each warehouse containing
information about shelves and specific SKU numbers and quantities

B) A Single Table
This table would contain all location information about the SKU
locations. Which warehouses it is in and which locations in the warehouse.


It seems to me that the first choice is better, but maybe I'm wrong - what's
your opinion? Thanks.

Option A is the only appropriate one.

You have a classic many-to-many relationship (one product can be kept at
many different warehouses, and one warehouse can keep many different
products).

To resolve many-to-many relationships, you need 3 tables: one to hold the
details on the products, one to hold the details on the warehouses, and one
to hold which products are being stored in which warehouses.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"dhstein" wrote in message
...
I'll try to describe the situation pretty thoroughly and then I have a
pretty
specific question. For an Inventory database, we are trying to keep track
of
2 pieces of information. One piece is the quantity of each item (each
item
is defined by an SKU number). The other piece is the location of each
item.
Items can be and will be in more than 1 warehouse. There are 4
warehouses
and each warehouse has hundreds of locations. One table is the Product
Table
which defines Vendors and other pieces of information. Now I want either
of
these two configurations:

A) Multiple Tables
1) Locations Table which contains a SKU and information about which
warehouse or warehouses hold the item
2) Warehouse Tables - a separate table for each warehouse
containing
information about shelves and specific SKU numbers and quantities

B) A Single Table
This table would contain all location information about the SKU
locations. Which warehouses it is in and which locations in the
warehouse.


It seems to me that the first choice is better, but maybe I'm wrong -
what's
your opinion? Thanks.

dhstein wrote:

There are 4 warehouses
and each warehouse has hundreds of locations.

I strongly suggest multiple tables because what happens when your
employer adds a 5th warehouse? Also it's much easier to find the
current locations of all of a particular item by reading multiple
records down rather than have to programmatically read sideways on a
single table.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

TblVendor
VendorID
Vendor contact fields

TblProduct
ProductID
ProductName
SKUNumber
VendorID
Other product desc fields
ProductPrice

TblWarehouse
WarehouseID
WarehouseName
Warehouse location fields

TblWarehouseLocation
WarehouseLocationID
WarehouseID
WarehouseLocation

TblProductWarehouseLocation
ProductWarehouseLocationID
WarehouseLocationID
ProductID
ProductInventory

I assumed the price of any product is independent of the warehouse it is in.

Steve



"dhstein" wrote in message
...
I'll try to describe the situation pretty thoroughly and then I have a
pretty
specific question. For an Inventory database, we are trying to keep track
of
2 pieces of information. One piece is the quantity of each item (each
item
is defined by an SKU number). The other piece is the location of each
item.
Items can be and will be in more than 1 warehouse. There are 4
warehouses
and each warehouse has hundreds of locations. One table is the Product
Table
which defines Vendors and other pieces of information. Now I want either
of
these two configurations:

A) Multiple Tables
1) Locations Table which contains a SKU and information about which
warehouse or warehouses hold the item
2) Warehouse Tables - a separate table for each warehouse
containing
information about shelves and specific SKU numbers and quantities

B) A Single Table
This table would contain all location information about the SKU
locations. Which warehouses it is in and which locations in the
warehouse.


It seems to me that the first choice is better, but maybe I'm wrong -
what's
your opinion? Thanks.

"Tony Toews [MVP]" wrote:

dhstein wrote:

There are 4 warehouses
and each warehouse has hundreds of locations.

I strongly suggest multiple tables because what happens when your
employer adds a 5th warehouse? Also it's much easier to find the
current locations of all of a particular item by reading multiple
records down rather than have to programmatically read sideways on a
single table.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Thanks for the information Tony

"Douglas J. Steele" wrote:

Option A is the only appropriate one.

You have a classic many-to-many relationship (one product can be kept at
many different warehouses, and one warehouse can keep many different
products).

To resolve many-to-many relationships, you need 3 tables: one to hold the
details on the products, one to hold the details on the warehouses, and one
to hold which products are being stored in which warehouses.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"dhstein" wrote in message
...
I'll try to describe the situation pretty thoroughly and then I have a
pretty
specific question. For an Inventory database, we are trying to keep track
of
2 pieces of information. One piece is the quantity of each item (each
item
is defined by an SKU number). The other piece is the location of each
item.
Items can be and will be in more than 1 warehouse. There are 4
warehouses
and each warehouse has hundreds of locations. One table is the Product
Table
which defines Vendors and other pieces of information. Now I want either
of
these two configurations:

A) Multiple Tables
1) Locations Table which contains a SKU and information about which
warehouse or warehouses hold the item
2) Warehouse Tables - a separate table for each warehouse
containing
information about shelves and specific SKU numbers and quantities

B) A Single Table
This table would contain all location information about the SKU
locations. Which warehouses it is in and which locations in the
warehouse.


It seems to me that the first choice is better, but maybe I'm wrong -
what's
your opinion? Thanks.


Thanks for your response Doug.

"Steve" wrote:

TblVendor
VendorID
Vendor contact fields

TblProduct
ProductID
ProductName
SKUNumber
VendorID
Other product desc fields
ProductPrice

TblWarehouse
WarehouseID
WarehouseName
Warehouse location fields

TblWarehouseLocation
WarehouseLocationID
WarehouseID
WarehouseLocation

TblProductWarehouseLocation
ProductWarehouseLocationID
WarehouseLocationID
ProductID
ProductInventory

I assumed the price of any product is independent of the warehouse it is in.

Steve



"dhstein" wrote in message
...
I'll try to describe the situation pretty thoroughly and then I have a
pretty
specific question. For an Inventory database, we are trying to keep track
of
2 pieces of information. One piece is the quantity of each item (each
item
is defined by an SKU number). The other piece is the location of each
item.
Items can be and will be in more than 1 warehouse. There are 4
warehouses
and each warehouse has hundreds of locations. One table is the Product
Table
which defines Vendors and other pieces of information. Now I want either
of
these two configurations:

A) Multiple Tables
1) Locations Table which contains a SKU and information about which
warehouse or warehouses hold the item
2) Warehouse Tables - a separate table for each warehouse
containing
information about shelves and specific SKU numbers and quantities

B) A Single Table
This table would contain all location information about the SKU
locations. Which warehouses it is in and which locations in the
warehouse.


It seems to me that the first choice is better, but maybe I'm wrong -
what's
your opinion? Thanks.


Steve thanks for the information and the table design.

"Steve" wrote:

TblVendor

Although, as I've posted in the past, I completely disagree with using
any kind of object prefixing such as tbl other than in VBA variables.

Tony's Object Naming Conventions
http://www.granite.ab.ca/access/tony...onventions.htm

Tony's Table and Field Naming Conventions
http://www.granite.ab.ca/access/tablefieldnaming.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

On Sun, 23 Nov 2008 17:13:27 -0700, "Tony Toews [MVP]" wrote:

"Steve" wrote:

TblVendor

Although, as I've posted in the past, I completely disagree with using
any kind of object prefixing such as tbl other than in VBA variables.

Tony's Object Naming Conventions
http://www.granite.ab.ca/access/tony...onventions.htm

Tony's Table and Field Naming Conventions
http://www.granite.ab.ca/access/tablefieldnaming.htm

Tony

If Steve were to drop the TblVendor nonsense and just name the table Vendors, if would be a giant
step in conveying the idea that he is truly a competent database professional. Appearance counts
for a lot when you are trying to sell yourself. Even though a database with names like TblVendor
could be designed correctly, the Tbl Vendor creates an unnecessary barrier for many people.

Of course I realize that prefixes Tbl, tbl, tbl_ , etc. have their defenders.

If you are such a "truly competent database professional", answer this:
Vendor appears in some VBA code, is Vendor a table or query?

Steve



"Michael Gramelspacher" wrote in message
...
On Sun, 23 Nov 2008 17:13:27 -0700, "Tony Toews [MVP]"
wrote:

"Steve" wrote:

TblVendor

Although, as I've posted in the past, I completely disagree with using
any kind of object prefixing such as tbl other than in VBA variables.

Tony's Object Naming Conventions
http://www.granite.ab.ca/access/tony...onventions.htm

Tony's Table and Field Naming Conventions
http://www.granite.ab.ca/access/tablefieldnaming.htm

Tony

If Steve were to drop the TblVendor nonsense and just name the table
Vendors, if would be a giant
step in conveying the idea that he is truly a competent database
professional. Appearance counts
for a lot when you are trying to sell yourself. Even though a database
with names like TblVendor
could be designed correctly, the Tbl Vendor creates an unnecessary barrier
for many people.

Of course I realize that prefixes Tbl, tbl, tbl_ , etc. have their
defenders.