Table Structure and Normalization

[tmyers]

I am revisiting a backend I made about 2 years ago at this point and trying to making improvements as I know a lot more than I did then and the old design isn't playing well with converting to a Python based front end. I have attached a picture of it. This is the backend for an application that facilitates the sending of request to our wire room to be cut as well as manages their inventory at a rudimentary level as our primary POS system doesn't provide the granular level we need for said inventory.

I have already done work in reworking several of the tables and splitting them apart a bit more, such as removing shipping details from tblTicket and other odds and ends. The main issue I am having is how to reconcile the tables tblWireCut, tblColorWire and tblCutColorWire as the first two pretty similar. The reason for the initial separation was I could not figure out how to handle reels containing multiple product at various length while also keeping a straight forward method for doing a typical one product request such as 3x50'.

How would I restructure the tables to handle essentially a container that can contain multiple types of product and various lengths? A single reel could potentially have 12 different products and 4 different lengths. Think of a giant wire spool that has 4 different color wire at one length stacked on top of another 4 set of various color wire at a different length and so on.

My initial thought was to eliminate tblColorWire and tblCutColorWire completely and place a new table between tblTicket and tblWirecut to facilitate this "container" that could have one item or many.

 

[Uncle Gizmo]

It strikes me that you are focusing on the table structures.

It might be beneficial to take a step back and examine the real-world requirements.

Map out the user cases – how does the wire room operate, what types of requests come in, how does the inventory vary, and what specifics does the Python front-end need to handle?

This understanding can then inform a more effective table design that mirrors the practicalities of your business operation, making it easier to manage the inventory with the granularity you require.

In essence, tackle the real-world use first; the tables will follow more naturally once you've got that pinned down.

 

[Uncle Gizmo]

Looking at it, I wondered if this might be a way to tackle the problem

tblReel: Possibly a new table or adapt the existing tblWireCut. It should uniquely identify each reel and can include attributes such as ReelID, TotalLength, Status.

tblProduct: Fields:- ProductID, Color, Type, and other product attributes.

tblReelComposition: A new table that would have foreign keys to tblReel and tblProduct. Each record would represent a length of a product on a reel, with fields:- ReelID, ProductID, LengthOfProductOnReel.

tblTicket could be modified to include a reference to the tblReel to show which reel an item was cut from.

 

[plog]

...how to handle reels containing multiple product at various length while also keeping a straight forward method for doing a typical one product request such as 3x50'...y a container that can contain multiple types of product and various lengths? A single reel could potentially have 12 different products and 4 different lengths. Think of a giant wire spool that has 4 different color wire at one length stacked on top of another 4 set of various color wire at a different length and so on.

Objects Words (possible tables): reels, products, requests, container,

Attribute Words (possible fields): length, product type, color

We're not as deep in the woods on this as you are. You threw a lot of information at us about fields and tables and relationships, but without knowledge of the real world process this is based on, it's hard to get a toehold into understanding your database. Give us a 2 paragraphs without any database jargon about what this organization does and what this database will help accomplish.

From 1000 feet above looking at your table structure the thing that sticks out most without knowing anything about the data is the circular paths your relationships create. I can trace 3 paths from tblTicket to tblWireRoom, that's not right, there should only be one path.

Also, the only table that has a Color field is tblPullReport. I'd assume it would be in tblColorWire if not upstream or from it.

 

[jdraw]

I agree with plog. I'd like to a see a description along the lines of
" a day at the office" or " processing a typical request".

 

[tmyers]

I agree with plog. I'd like to a see a description along the lines of
" a day at the office" or " processing a typical request".

I do tend to overload things with info that isn't needed. If I have a toxic trait, that would be one lol.

Process:
Salesman writes in order in our primary POS system and creates the sales order. Products that need cut (wire, flexible conduit) and then entered into this database as a request that is then sent to our wire room to be cut. A typical order could be something along the lines of this:

Ticket 1234567:
1 x 18' SER6/3
1 x 35' AL034
3 x 35 ALTHHN1STRBLK


Ticket 7654321:
Reel 1 consisting of:
Layer 1 -
1 x 350 THHN350STRBLU
1 x 350 THHN350STRRED
1 X 350 THHN350STRGRY

Layer 2 -
4 X 150 ALTHHN750BLK

Layer 3 -
1 x 500 THHN600RED
1 x 500 THHN600STRBLU
1 x 500 THHN600STRGRY
1 x 500 THHN600STRYEL

As per above, some tickets could consist of individual items that have no relation to one other and are cut by themselves/separately. Others can be like the second where a single reel/container ends up with various lengths and combination of product that has to be ran in a specific order hence the layer order. For the record, I used our actual part numbers for the product and in regards to colored wire, the color is always the last piece of the part number.

 

[jdraw]

In #6 are these all wire or is flexible conduit also involved?
What exactly does layer 1/2/3 represent?

Do you "package" the item(s) to be delivered ( multi-wire/multi-product ) on a large spool? If so, is this the typical "item" sent to the customer.
Please realize that most readers (me at least) are not familiar with the type of request and satisfaction process that you seem to be dealing with. So the more info you can supply, the better your chance of getting more focused responses.

 

[Pat Hartman]

Most of us can envision the first type of order. Each wire type is cut to a specific length and then wound around a spool like thread or on it self with the circle secured by a twisty as we see with computer cords. The layer description is a mystery though. Are the wires spliced and then wound on a single spool or a self-circle? What is the second nesting about?

 

[tmyers]

Imagine a large wood or metal reel/spool. We have a machine that can pull up to 4 different wire at the same time (all the same AWG size) onto that reel. We can then "stack" or "layer" it on top of each other until we reach the reels capacity which varies depending on the size of wire being ran onto it.

Maybe this video of one of our reels being used in the field might shed a little light on what I am attempting to describe:

In my example for the second ticket, each layer is essentially an entire "unit" as all wire for that layer will go onto and be removed at the same time and we can stack multiple layers on top of each other on a given reel.

In #6 are these all wire or is flexible conduit also involved?
What exactly does layer 1/2/3 represent?

Do you "package" the item(s) to be delivered ( multi-wire/multi-product ) on a large spool? If so, is this the typical "item" sent to the customer.
Please realize that most readers (me at least) are not familiar with the type of request and satisfaction process that you seem to be dealing with. So the more info you can supply, the better your chance of getting more focused responses.

In my example only the second item on the first ticket is flexible conduit. I honestly probably should have omitted it as it just muddies the water more. You can ignore that bit as it has no bearing on the second part.

That video will likely clear up what I am attempting and failing at describing.

Edit:
I think my brain finally thought of a way to describe it! Think of it as kits. We can have a reel that can have multiple kits of up to 4 different colors/wires per kit and could potentially have up to 6 kits per reel.

 

[Uncle Gizmo]

Maybe this video of one of our reels being used in the field might shed a little light on what I am attempting to describe:

That's a fantastic idea, makes cable installation a breeze!

 

[Pat Hartman]

The video was very useful. One more question.

Each of the "layers" is a "kit". Are the kits standard in that Kit 1 = Blue, Red, Gray, Kit 2 = Red, Green, Blue, Kit 3 = Red, Blue, Yellow, Black? I'm assuming that all wires in a "kit" would be the same length although they might be different sizes. If the "kits" are made on the fly, then the structure has to be more complex.

A structure that is always the same is easier to design than one that varies. For example:
Reel, Layer, Wire would be three nested tables under the Ticket.

For example 1 we have 3 Reel rows. Each row has a separate Reel #. Each Reel has 1 layer a row nd each layer has 1 wire row
For example 2 we have 1 Reel row and so only a single Reel #. The single Reel has 3 layer rows , each with a different layer #. Layer 1 has 3 wire rows, Layer 2 has 1 wire row, Layer 3 has 4 wire rows. The Length goes on the Layer row and the width/gage and color goes on the Wire row.

This is different from your schema and should be simpler.

By convention, the Layers would be numbered from the inside out (how the wires are laid onto the reel or from the outside in (how the wires come off the reel. I don't know which but they would be consistent based on your customer's expectation.

 

[tmyers]

Each of the "layers" is a "kit". Are the kits standard in that Kit 1 = Blue, Red, Gray, Kit 2 = Red, Green, Blue, Kit 3 = Red, Blue, Yellow, Black? I'm assuming that all wires in a "kit" would be the same length although they might be different sizes. If the "kits" are made on the fly, then the structure has to be more complex.

Each "kit" would all be the same length and size as different sizes come off at different speeds so you cannot mix them. There are standard color combinations per building code but we do see instances of unusual combinations that would prevent preset color combinations unfortunately.

By convention, the Layers would be numbered from the inside out (how the wires are laid onto the reel or from the outside in (how the wires come off the reel. I don't know which but they would be consistent based on your customer's expectation.

We follow the first layer to come off the reel is layer 1, so layer 1 would be the LAST layer put onto the reel.

A structure that is always the same is easier to design than one that varies. For example:
Reel, Layer, Wire would be three nested tables under the Ticket.

For example 1 we have 3 Reel rows. Each row has a separate Reel #. Each Reel has 1 layer a row nd each layer has 1 wire row
For example 2 we have 1 Reel row and so only a single Reel #. The single Reel has 3 layer rows , each with a different layer #. Layer 1 has 3 wire rows, Layer 2 has 1 wire row, Layer 3 has 4 wire rows. The Length goes on the Layer row and the width/gage and color goes on the Wire row.

This was how I thought it would likely end up where each cut whether it technically ends up on a reel or not would go through the same process but could determine if that was the best method or not. Despite all nuances, there is really only two options in the grand scheme of things, the wire is either coiled on itself or put onto a reel, granted there are 3 different types of reels that could be used.

Question though, would quantity go in the layer table as well? Such as 3 x some length?

That's a fantastic idea, makes cable installation a breeze!

Off topic but that is actually the entire selling point of those! You used to have to have a reel for each conductor or would use what we called compartment reels which had a pretty limited capacity. Either option would take several workers and equipment to setup to pull the wire but with these you can do it with as little as two people.

Those reels are insanely easy to move despite being made of steel and having hundreds if not a few thousand pounds of wire on them. The one in the video is the smaller one but we have one of the large ones in the building as a demo that I think weighs several thousand pounds with wire on it and recently had college kids here and did a demo and a girl who was maybe all of 5' was able to easily roll it around. They are pretty neat!

 

[tmyers]

Attached is what I have so far with the new backend. Hopefully I understood where you were going to the reel, layer and cut tables. I also tried a similar approach on the inventory side to try and keep track of such a reel should we accept a return or something and have to track it as individual but also identify it as a piece of something else.

 

[Pat Hartman]

Question though, would quantity go in the layer table as well? Such as 3 x some length?

If the sizes and lengths are all the same for a layer, then both size and length goes in the layer even though that doesn't make much sense for a single wire. It is just easier to have ONE structure than multiples.

@tmyers Thanks for redoing the schema.
1. I don't see a need for tblShipping or tblItcketDetails. Both sets of data are 1-1 with ticket and so should be in the ticket table.
2. I would not use the kit path unless you actually inventory the prebuilt reels. Given that the lengths would be different depending on the installation, I would say that all Reels/Layers are custom.
3. The tblWire does make some sense because you need to know how much wire you have left on your source so you can pick the best fit from inventory.

PS, I see your pattern for FK names but it is not consistent. Consistency is your friend. You should never have to think about a name as you are coding. Hmmmm Is it Customer or Cust in this case? So in Ticket, it would be ID_Cust and ID_Ship - except of course that the shipping data should be directly in this table. Also ID_Real in components should be ID_Wire.

And finally, If different reels have different capacities, then you should have a table that defines reals so that the max layers and max length can be specified for each reel

 

[Uncle Gizmo]

ID_Real

Should that be ID_Reel ?

 

[Pat Hartman]

Picky, picky, picky

 

[Uncle Gizmo]

Picky, picky, picky

And I can't stand fishing, and I don't mean "not sitting down"

 

[tmyers]

I plan on doing another pass over to make sure all my keys and such match my convention, which as Pat pointed out is me inverting the PK when it is a FK. I found that to be a really simple way to make things match and be o

1. I don't see a need for tblShipping or tblItcketDetails. Both sets of data are 1-1 with ticket and so should be in the ticket table.
2. I would not use the kit path unless you actually inventory the prebuilt reels. Given that the lengths would be different depending on the installation, I would say that all Reels/Layers are custom.
3. The tblWire does make some sense because you need to know how much wire you have left on your source so you can pick the best fit from inventory.

For #1 I originally had it all in the table but was honestly not sure so decided to break it apart this time through but will recombine them.

As for #2, there are instances of a reel like this making its way back into inventory for a multitude of reasons. I thought the kit method would allow me to track those kind of instances but in all honestly could be handled with a simple note field to denote something is part of set/kit. Overall they are rare and don't happen often, maybe a few times a year if that.

You are correct for #3 in that it is my inventory table to track what sources are available.

And finally, If different reels have different capacities, then you should have a table that defines reals so that the max layers and max length can be specified for each reel

We have 3 different reels each with a different capacity but I feel this would be REALLY hard to try and set as their capacity is based on the AWG of the wire being loaded on them and that you can have so many variations/combinations.

My next step will be adding back in the various support tables such as logging the big reels to know which ticket they went out on and such.

 

[Pat Hartman]

Looks like the schema is much simpler now than when you began. Work with it a bit to see how your forms will look and the queries you need for reporting.

 

[tmyers]

Looks like the schema is much simpler now than when you began. Work with it a bit to see how your forms will look and the queries you need for reporting.

It is. It looks like a much cleaner approach now so thank you so much for your help!
Downside now is a large portion of my front end thats based in Python will have to be reworked to account for the schema change