K
Kelii
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
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