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.

 

[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!

 

[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.

 

[Uncle Gizmo]

ID_Real

Should that be ID_Reel ?

 

[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.

 

[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

 

[gemma-the-husky]

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.

I agree that you should start with the data.

But I struggle to see how a "reel" can contain multiple products and lengths. Your items gave to be atomic. Obviously any length can be cut from a reel, or from a bar, but that's a different thing. I just don't see how an "atomic" item called a reel can manage multiple discreet products.

 

[tmyers]

Sorry about that but that is why we always emphasize stabilizing the schema before moving on. Rushing to create objects and code is generally a waste of time and will ultimately keep you locked into a bad design decision because you have so much invested in it that you think that fixing it is better than going back and doing it again. I've been designing developing since 1968 and if there has been one painful lesson that I've learned is - once you realize your schema does not support the business, you are just wasting your time trying to save it. Fix the problem and then fix the objects that the schema fix broke. The reason this lesson is so painful is that most of us need to learn it more than once and each time we say - never again. Just stop the pain and cut off the limb at the ankle because if you don't do that, you will be later cutting it off at the knee.

Designing the new frontend is what made me realize just how badly designed the backend was and quite quickly at that lol.

While I still kind of stink at database design, I have improved a lot when it comes to my coding in that my modules are very decoupled from one another so sweeping changes aren't too hard to manage. Plus the front end design is still very much in its early stages so it should just take a couple tweaks to my data access layer and few changes to my data binds in the view to make things jive again.

I agree that you should start with the data.

But I struggle to see how a "reel" can contain multiple products and lengths. Your items gave to be atomic. Obviously any length can be cut from a reel, or from a bar, but that's a different thing. I just don't see how an "atomic" item called a reel can manage multiple discreet products.

As Pat said, if you take a peak at the video it may make more sense at what I was attempting to poorly describe. Pat made a good analogy to a box from Amazon containing multiple items in that a reel is more or less just a container that can hold a lot of product but each "set" or layer as I called it must match as far as wire gauge is concerned. Each of these reels can have hundreds or even thousands of feet of wire on each depending on the size of wire loaded onto them.


Once again I wanted to thank you for all your help Pat.

 

[gemma-the-husky]

There you go. Stepwise refinement. It's well worth getting a data structure like this sorted correctly, but in this case not easy at all, and likely to take some iterations.

Hopefully the time spent successively redesigning a database proves a worthwhile investment..