A question of normalization

I've been developing an application for 2 years now that one may refer
to as an inventory management database. I use the application on a
daily basis to help run my business and it works nicely. I've recently
begun to redesign the application and have decided that I come to a
crossroads that is well beyond my expertise.

I have 3 key tables:
1. tblItems: list of raw materials and other relevant information (12
fields in total)
2. tblSubAssemblies: list of sub assembly items and relevant info (9
fields in total)
3. tblFinishedProducts: list of finished products for resale and
relevant info (6 fields in total)

Some relevant information:
1. Each field in each of the three tables is repeated in at least one
of the other tables (i.e., category, type, Unit of Measure, Reorder
Point)
2. Sub Assemblies consists of a combination of raw materials and other
Sub Assemblies
3. Finished Products consists of a combination of raw materials and
Sub Assemblies
4. A key function of the application is to generate a "costed" bill of
materials
5. The application does most of what you would expect for an inventory
management database (e.g., invoice entry, inventory entry, sales
entry, perpetual inventory analysis, LIFO valuation)

My question:
Should I consolidate each of the 3 key tables into 1 table, or is this
just asking for major trouble (i.e., breaking normalization rules)? I
believe that 1 table yields a more elegant solution for coding the
interface and I have identified specific areas where the interface
would become much more user friendly as a result of the aggregation.

I realize that many of you have deep experience in this area, and I
wonder if there is a generally accepted solution with regards to my
question in inventory management applications.

Thanks in advance for your help,

Kelii

Comments inline;

1. Each field in each of the three tables is repeated in at least one
of the other tables (i.e., category, type, Unit of Measure, Reorder
Point)

Perhaps this is a typo. Did you really mean to say that *every* field in
your app is repeated at least once in another table? If so you have
definitely have a design problem.

2. Sub Assemblies consists of a combination of raw materials and other
Sub Assemblies
3. Finished Products consists of a combination of raw materials and
Sub Assemblies

I'm confused (which is not unusual :-)). If you combine a raw material and a
sub assembly, does it become another sub assembly or a finished product? If
it can be either, how do you determine which it is?

5. The application does most of what you would expect for an inventory
management database (e.g., invoice entry, inventory entry, sales
entry, perpetual inventory analysis, LIFO valuation)

Maybe, but it's probably bloated with a ton of redundant data (assuming that
your first comment above is not a typo).

Should I consolidate each of the 3 key tables into 1 table, or is this
just asking for major trouble (i.e., breaking normalization rules)?

No you shouldn't (do that) and yes it is (asking for major trouble).

I would recommend you do some research on proper relational database design
before you start redesigning your app. There is a good book called "Database
Design for Mere Mortals" by Michael Hernandez that is recomended frequently
in this forum. Also here are some very good online sources.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

--
_________

Sean Bailey

Sean:

Perhaps this is a typo. Did you really mean to say that *every* field in
your app is repeated at least once in another table? If so you have
definitely have a design problem.

There is no typo, but perhaps you misunderstood or my verbage is not
accurate. For example, Items have a description field (i.e., text), so
do Sub Assemblies and Finished products. Another example, Items and
Sub Assemblies both have Units of Measure (e.g., Lb, Oz, Can). I don't
believe that this conflicts with 3NF, as each item must be inventoried
against its own UOM. There is a separate tblUOM with referential
integrity back to the Item, SA (Sub Assembly), and Finished Products
tables. There are several other examples, such as Active Status,
Reorder Point, Category, Type, Location etc...

I'm confused (which is not unusual :-)). If you combine a raw material and a
sub assembly, does it become another sub assembly or a finished product? If
it can be either, how do you determine which it is?

Yes, this is true. A Raw Material may be combined with an SA to
produce either an SA or a Finished Product. The difference between the
two is: a Finished Product is sold to a customer (note: there are
separate tables for Sale, Date of Sale, etc...), whereas an SA is not
sold to customers.

Maybe, but it's probably bloated with a ton of redundant data (assuming that
your first comment above is not a typo).

I hope this is clear after my explanation above; to the best of my
knowledge the data is not redundant.

No you shouldn't (do that) and yes it is (asking for major trouble).

Given above clarification, do you still think so? If so, what trouble
am I likely to run into?

I would recommend you do some research on proper relational database design
before you start redesigning your app. There is a good book called "Database
Design for Mere Mortals" by Michael Hernandez that is recomended frequently
in this forum. Also here are some very good online sources.

Thank you, I'm always up for reading more theory (no joke).

Kelii

OK, so first I'm going to take a stab at the table structure you have now
based on your posts, then I'll move on to how it should be. Keep in mind
that I know very little about your db, so I'm just winging it with table and
field names. I'll use three tables (Items, SubAssemblies and FinishedProducts)
and I'll assume that a finished product consists of one Item combined
with one SubAssembly.

I'm not going to tackle the issue of how a raw material combined with a sub
assembly is still a sub assembly, because this inherently doesn't make sense
to me. If you combine a sub assembly with something else, it seems to me
it should become something else (an assembly perhaps?). Nevermind, I'm just
rambling.

So, based on your posts, it sounds like you have something similar to the
following;

Items Table
*********
ItemID (Primary Key)
ItemName
ItemDescription
ItemUnitOfMeasure
ItemCost
Other fields related to item

SubAssemblies Table
****************
SubAssyID (Primary Key)
SubAssyName
SubAssyDescription
SubAssyUnitOfMeasure
SubAssyCost
Other fields related to SubAssembly

FinishedProducts Table
*****************
ItemID (Combined with SubAssyID for PK)
ItemName
ItemDescription
ItemUnitOfMeasure
SubAssyID (Combined with ItemID for PK)
SubAssyName
SubAssyDescription
SubAssyUnitOfMeasure
Other fields related to finished product

The above scenario is wrong, because I am repeating ItemName,
ItemDescription and ItemUnitOfMeasure (and also for SubAssembly) in
the FinishedProducts table. From your posts, it sounds like that is what
you are doing.

Here's how the above scenario should look;

Items Table
*********
ItemID (PK)
ItemName
ItemDescription
ItemUnitOfMeasure
ItemCost
Other fields related specifically to the item

SubAssemblies Table
****************
SubAssyID (PK)
SubAssyName
SubAssyDescription
SubAssyUnitOfMeasure
SubAssyCost
Other fields related specifically to the SubAssembly

FinishedProducts Table
*****************
ItemID (Combined with SubAssyID for PK)
SubAssyID (Compined with ItemID for PK)
FinishedProductDescription
Any other fields that are related specifically to the FinishedProduct

It is only necessary to store the ItemID and SubAssyID in the
FinishedProducts table. All of the other values related to those two
can be retrieved from their respective tables whenever you need
them. There are many different ways to do this (combo boxes, DLookup, etc.)
depending on your preferences, but the point is you don't need to store those
other values in the FinishedProducts table.

The same goes for the other tables in your db. One exception might
be if you have a table of sale transactions, then you may need
to store the FinishedProduct cost in that table if the costs can change
over time and you need the cost at the time of the sale.

As far as what problems you'll encounter if you put everything into
one large table, probably too many to list, but let's just take one
scenario.

Let's suppose you're going to sell products (which you obviously do).
If you have everything in one table, then you've got to have fields like;

SaleItem1
SaleItem2
SaleItem3
SaleItem4

But how do you decide how many SaleItem fields you should have?
How many items can a customer purchase in one transaction?
Now let's suppose you decide to have ten. First of all, every time a
customer purchases only one item, you end up with at least
nine empty fields in that record. Second, you will inevitably have
a situation where a customer wants to purchase more than ten
items, so you will have to go back and add more fields to your
table. Either that, or you will have to create more than one record
for the same purchase.

Like I said, there are a host of other problems you'll encounter
with the one table approach. I would strongly suggest that you
don't try this. I speak from experience. My first attempt at db
design (some time ago) was one large table and it was a complete
disaster.

--
_________

Sean Bailey

On Feb 19, 12:09*am, Beetle wrote:
OK, so first I'm going to take a stab at the table structure you have now
based on your posts, then I'll move on to how it should be. Keep in mind
that I know very little about your db, so I'm just winging it with table and
field names. I'll use three tables (Items, SubAssemblies and FinishedProducts)
and I'll assume that a finished product consists of one Item combined
with one SubAssembly.

I'm not going to tackle the issue of how a raw material combined with a sub
assembly is still a sub assembly, because this inherently doesn't make sense
to me. If you combine a sub assembly with something else, it seems to me
it should become something else (an assembly perhaps?). Nevermind, I'm just
rambling.

So, based on your posts, it sounds like you have something similar to the
following;

Items Table
*********
ItemID (Primary Key)
ItemName
ItemDescription
ItemUnitOfMeasure
ItemCost
Other fields related to item

SubAssemblies Table
****************
SubAssyID (Primary Key)
SubAssyName
SubAssyDescription
SubAssyUnitOfMeasure
SubAssyCost
Other fields related to SubAssembly

FinishedProducts Table
*****************
ItemID (Combined with SubAssyID for PK)
ItemName
ItemDescription
ItemUnitOfMeasure
SubAssyID (Combined with ItemID for PK)
SubAssyName
SubAssyDescription
SubAssyUnitOfMeasure
Other fields related to finished product

The above scenario is wrong, because I am repeating ItemName,
ItemDescription and ItemUnitOfMeasure (and also for SubAssembly) in
the FinishedProducts table. From your posts, it sounds like that is what
you are doing.

Here's how the above scenario should look;

Items Table
*********
ItemID (PK)
ItemName
ItemDescription
ItemUnitOfMeasure
ItemCost
Other fields related specifically to the item

SubAssemblies Table
****************
SubAssyID (PK)
SubAssyName
SubAssyDescription
SubAssyUnitOfMeasure
SubAssyCost
Other fields related specifically to the SubAssembly

FinishedProducts Table
*****************
ItemID (Combined with SubAssyID for PK)
SubAssyID (Compined with ItemID for PK)
FinishedProductDescription
Any other fields that are related specifically to the FinishedProduct

It is only necessary to store the ItemID and SubAssyID in the
FinishedProducts table. All of the other values related to those two
can be retrieved from their respective tables whenever you need
them. There are many different ways to do this (combo boxes, DLookup, etc.)
depending on your preferences, but the point is you don't need to store those
other values in the FinishedProducts table.

The same goes for the other tables in your db. One exception might
be if you have a table of sale transactions, then you may need
to store the FinishedProduct cost in that table if the costs can change
over time and you need the cost at the time of the sale.

As far as what problems you'll encounter if you put everything into
one large table, probably too many *to list, but let's just take one
scenario.

Let's suppose you're going to sell products (which you obviously do).
If you have everything in one table, then you've got to have fields like;

SaleItem1
SaleItem2
SaleItem3
SaleItem4

But how do you decide how many SaleItem fields you should have?
How many items can a customer purchase in one transaction?
Now let's suppose you decide to have ten. First of all, every time a
customer purchases only one item, you end up with at least
nine empty fields in that record. Second, you will inevitably have
a situation where a customer wants to purchase more than ten
items, so you will have to go back and add more fields to your
table. Either that, or you will have to create more than one record
for the same purchase.

Like I said, there are a host of other problems you'll encounter
with the one table approach. I would strongly suggest that you
don't try this. I speak from experience. My first attempt at db
design (some time ago) was one large table and it was a complete
disaster.

--
_________

Sean Bailey

All that's needed are the two tables, the items table, which contains
all types of items, assemblies, sub-assemblies, sub-sub-assemblies,
sub-sub-sub-subsub-assemblies, and parts, and a structure table which
contains the parent part ID, the Child Part number and the quantity
per parent, and optionally an identifier code.

The primary key of this table is Parent ID and Child ID.
It can be used recursively for nesting sub-assemblies to an
undetermined level dependent on memory in the computer used to hold
the data. I've got as many as 12 levels on some items.

Example:
Car
-Drive Train
--Engine
---Fuel Pump
----Housing Assembly
-----Housing Casting
-----Threaded Insert
-Body
...etc.



--

rquintal

Fabulous, I'm so happy that you were able to fully grasp my (poorly
phrased) question. It also sounds like you are implementing this table
structure which give me hope.

Follow up question for you, just so that I'm clear:
Can your app handle the following input

Housing Assembly
-Part A
-Part B
-Part C

Car
-Drive Train
--Engine
---Fuel Pump
----Housing Assembly

Meaning, your firm manufactures the housing assemblyl which constitues
a portion of the Fuel Pump.

Furthermore, when your app costs out the bill of materials on the car,
it accounts for the sum total of Parts A, B, and C used in the Housing
Assembly along with all of the other 3rd party sourced materials.

Based on your post, it sounds like the app can do this, but I just
wanted to be completely sure.

Thanks again,

Kelii

On Feb 26, 8:40*pm, Kelii wrote:
rquintal

Fabulous, I'm so happy that you were able to fully grasp my (poorly
phrased) question. It also sounds like you are implementing this table
structure which give me hope.

Follow up question for you, just so that I'm clear:
Can your app handle the following input

Housing Assembly
-Part A
-Part B
-Part C

Car
-Drive Train
--Engine
---Fuel Pump
----Housing Assembly

Meaning, your firm manufactures the housing assemblyl which constitues
a portion of the Fuel Pump.

Furthermore, when your app costs out the bill of materials on the car,
it accounts for the sum total of Parts A, B, and C used in the Housing
Assembly along with all of the other 3rd party sourced materials.

Based on your post, it sounds like the app can do this, but I just
wanted to be completely sure.

Thanks again,

Kelii

Yes, this is what any ERP (Enterprise Requirements Planning)
application is designed to do.