Normalization? Is it needed? 2

[BJNK]

Hello, I would like to first apologize for what could be a long winded post. I would also like to say what a cool site this is and I can't believe I just found it!

Ok so heres the rundown. I just recentley started a job at a furniture design company. They have been around for about 18 years and have never had a solid catalog or any sort of information process.(!!!) No database, no records of past or present products, just random excel sheets, some with pictures some without and all products just named on a whim. The guys in the back doing the building have the info in their heads, they get work orders and know what to do. They write stuff down on wood. lol So to say this is an interesting and challenging project is an understatement. This is my first "real world" database job. I feel confident in my skills and knowledge to take it on and Im enjoying it. Oh I should also say they sell only to companies and usually large quantities for a length of time, not a lot of quick product changes.

Now onto my question. I will use for example their TV stands. To bring them into the manufacturing side of the database I have the following design:

TVS_ID(PK)| Wood_ID(FK) | Finish_ID(FK) | Height | Width | Depth | Door_ID(FK) | Door_Quantity | Shelf_ID(FK) | Shelf_Quantity | Drawer_ID(FK) | Shelf_Quantity | Handle_ID(FK) | Handle_Quantity | TopMoulding_ID(FK) | BotMoutlding_ID(FK) | FaceMoulding_ID | FaceMoulding_Quantity |

Ok now I do realise that there are dependincies that are not just the primary key. I know that there are several of them. And I know that this goes against everything I've been taught about databases and normalization. This is the but. All of those things thrown together create one product, say TVS335. Now anytime someone wants to order that they can. But if another company wants to order the same thing but with glass doors and different handles it would cause an anomoly. The thing is, any time a company wants something differnt, which does not happen that often, MY company wants to save that new design as a product, say TVS350.

Can I leave my TV Stand table as is and get away with the anomoly because anytime it is changed they want to keep it? Can I just copy all the information from TVS335 and create a new record as TVS350 then edit the parts? Or am I doomed to a disaster down the road? This would then force me to have a TV Stand Table for each type, with doors (TVSTAND_DOORS), with no doors (TVSTAND_NDOORS), with shelves (TVSTAND_SHELVES) etc etc... And my problem with having it all combined from their tables in an order table, is that they want to save the new design... is this possible by drawing all the info through the

It may just be information overload on my part right now because I am sifting through hundreds of products that I am not yet familiar with, paired with the fact that no one here understands why I need to know the things I ask about lol. That may be causing me to overlook the obvious. Maybe. But can someone PLEASE show me the error of my ways and shed some light onto this for me?

Any help is really appreciated, and I apologize again for the length. Thank you!

 

[Remou]

How about a table of products types, a table of product properties and a table that joins these two?

[tt]tblProductType
PTID Description
1 TV Stand
2 Wardrobe

tblProperties
PrpID Description
1 Glass Doors
2 Shelves
3 Fancy bits

tblProductBuild
BuildID PTID PrpID
1 1 2
1 1 3
2 2 1[/tt]

I cannot say I have thought this through carefully, but you can see that such a set-up would allow easier sorting of main and sub types.

 

[lespaul]

What you are describing is a Bill of Materials. Accounting packages are designed to allow users to create a multi-component piece and use other pieces of inventory and reduce the inventory based on its bill of material. For instance, I build gadgets. A gadget is made up of:

3 widgets
4 sprockets
10 6" metal rods


So I create my items table looks like:
[tt]
Items
ItemID ItemDescription BOMID
1 Widget
2 Sprocket
3 6" Metal Rod
4 Gadget 1

Then I have a BOM table:
BOM
BOMID ItemID Qty
1 1 3
1 2 4
1 3 10
[/tt]
You will need a Create Inventory where the user can select items that have a BOMID and enter the inventory item to create and how many. Say the user has made 5 gadgets. In your application you will need to track inventory additions and subtractions. You will run a process that adds 5 gadgets and subtracts 5*Qty required for all the other items.

Now let's say we've made an improved gadget and it has the following BOM:
5 widgets
6 sprockets
15 6" metal rods
4 2" wheels
2 3" axles

To add this new item, all you have to do is add to the Items table and BOM to identify what components make up an improved Extreme Gadget:
[tt]Items
ItemID ItemDescription BOMID
1 Widget
2 Sprocket
3 6" Metal Rod
4 Gadget 1
5 2" Wheel
6 3" Axle
7 Extreme Gadget 2

BOM
BOMID ItemID Qty
1 1 3
1 2 4
1 3 10
2 1 5
2 2 6
2 3 15
2 5 4
2 6 2[/tt]

Now you have a new item available to build (extreme gadget) and all it's required components available to your existing application with no programming changes required.

That is the power of normalization. When your data is normalized you should not have to make any programming changes to adapt to modifications or additions to the processes you have developed.




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[BJNK]

Ok I really appreciate both of your input, and I definitley recognize that you guys are correct and that my setup was an embarrsement to normalization. lol

Note:This post has changed drastically as I've gone along so hopefully it makes sense...

The problem I have is that all of our products start as raw materials that then must be cut, shaped assembled and all in different phases before everything is assembled in the end before it gets stained.

Here are some tables that I'm now scheming about after your input, and there are problems with them, but hopefully were walking in the right direction.

tblProductClass

PCID Descrition
TVS TV Stand
EC Entertainment Center

tblProductProperties

PrpID Descrition
PN4872 Pine 48wX72hX6d
DR001 Colonial Style Door
DR002 Smoked Glass Door
TRM001 Colonial Trim Top
TRM002 Colonial Trim Bottom
FN001 Sandstone
FN002 Walnut

tblProductFinished

PFID PCID PrpID Dimensions Quantity
EC533 EC 40wX60hX30w
EC533 EC PN4872 ???? 4
EC533 EC DR001 ???? 2
EC533 EC DR002 ???? 2
EC533 EC TRM001 ???? 1
EC533 EC TRM002 ???? 1
EC533 EC FN002

tblMachine

MchID Description
1 Panel Saw
2 Plainer
3 Laithe

tblMachineWorkload

MchID ItemID PFID Dimensions Quantity
1 PN4872 EC533 40wX60h 18
1 OK4872 EC535 48wX60h 12
2 PN4872 EC533 4d 12

Does that make sense at all? In the end we want each workstation/machine to have its own work load assigend each week, based off of orders. That way they would have to know that each they need 18 PN4872's cut to the new dimensions, and the plainer would know they need 12 PN4872 trimmed to 4 inches.

Here are some of the problems that I can readily identify. First the dimensions in the finishproducts table. I need a way to specify the final dimensions of the product as it is extremely important. The dimensions involved ie:doors could be given in the FinishedProduct table which would make sense as doors rarely have the exact same dimensions from product to product. But I also feel that each property needs its own table to identify its own properties. For example the SW Glass door, is composed of 4 Pieces of wood (4wX16hX1d) arranged in a square, with 3 pieces of wood cascading down from 6-4-2inches to create style, and a piece of glass that fits in the middle. Now of course it would have a unique PrpID, but how do I let all of its properties be known, especially for build purposes? I think the doors and other such pieces need their own table, im just confused as to how I would do that without completely reverting back to the first mess of a table I was trying to creat.

Again any help is greatly appreciated! I seem to be in a bit of a mental fog, and it's clearing up, but I need some help to get me pointed in the right direction!

Thank you!

 

[Remou]

You can add layers down as well as up, lespauls's post starts with a gadget that needs to be built. To build a Colonial Door you need,

[tt]tblMaterials
MatID Description Size Consider other tables
1 Wood 10 x 6 ->Size table
2 Beading 20 x 1 ->Styles table
3 Handles
4 Varnish ->Colour table
5 Screws 5

tblBuild
PartID MatID Colour CutTo Machine Time
DR001 1 A4 4 x 4 B24 1.05
DR001 4 123[/tt]

Once again, this is just thowing suggestions at you.

 

[lespaul]

For example the SW Glass door, is composed of 4 Pieces of wood (4wX16hX1d) arranged in a square, with 3 pieces of wood cascading down from 6-4-2inches to create style, and a piece of glass that fits in the middle.

So in your Items (RawInventory) you would need:
[tt]
ItemID ItemDescription BOMID
1 4x16x1 pine
2 6" trim
3 4" trim
4 2" trim
5 4x4x1/4" glass pane
6 SW Glass Door 1

[/tt]

now here's the thing. Let's say your 6" trim comes in 25' long sections. You need to use 5' for each door, so you have another inventory item:
[tt]
ItemID ItemDescription BOMID
7 5' 6" trim - cut
[/tt]

now you have a "new" inventory item. You can create 5 pieces of 5' 6" trim (item #7) from each single piece of raw 6" trim (item #2). Now to make a door you would use 4 pieces of item #7.

Finished products are still inventory and you've taken existing inventory and used that to create new inventory.

Here's a link to an inventory tracking database template for Access 2003 that may help you.

Have you read the fundamentals link below? It's a great resource for normalization techniques.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi