Table Structure and Normalization

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

 

[Pat Hartman]

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

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.

 

[Pat Hartman]

But I struggle to see how a "reel" can contain multiple products and lengths. Your items gave to be atomic.

Did you look at the video? It shows clearly how a reel works. The layer is a little less clear. I might have called it a bundle. A layer is 1 or more types of wire of the same size and length that are connected together at the beginning and the end so they can be pulled through a conduit all together. So instead of pulling 4 50' 2" cables separately, you pull all four at one time. Then the next layer is a different configuration of cables, maybe 3 4" cables that are also 50' long. Now instead of 7 "pulls", you've only had to do 2 "pulls" so the installation time is dramatically reduced.

I just don't see how an "atomic" item called a reel can manage multiple discreet products

Can a box from Amazon hold multiple different products? Can a "cake mix" box contain a packet of cake mix and a packet of frosting? A reel is a specialized type of storage system. Have you ever seen a spool of Christmas ribbon that has 8 different colors and widths? That was the first attempt at solving this multiple cables problem. The "layer" concept solved many of the problems the separated solution couldn't solve.

 

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

 

[Pat Hartman]

You are very welcome. And thank you I learned something new too. I'm pretty sure I'd seen this wire concept before but never thought about how to make it work in a database until now. If there is no traffic and there seem to be men standing about (I don't want to distract anyone actually doing something), I have on multiple occasions stopped to talk with the workers and ask what they are doing. It's amazing what you can learn about the world around you as you watch workers upgrading infrastructure.

 

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