Table Size

[dkubiak]

Is there any advantage to breaking up data into a group of smaller tables if it can also be combined into one very large table?

 

[Andrew Fergus]

Rather than add the characterisitic of the value to the readings/defects table, the characteristic would be recorded in the defect table. To convert the value of the loss / defect / variance into a standard measure (weight or paper etc per your example) you would do that in a query by applying your calculation to the value from the reading/defect table and the characteristic from the defects table. In other words, in your query you can use 'if' statements to differentiate between the differing characteristics and then apply the appropriate calculation against the recorded defect value.

Does that make sense?
Andrew

 

[Andrew Fergus]

What is an example of a defect that would have differing units of measure in differing departments?
Andrew
[Edited due to re-reading an earlier post]

 

[dkubiak]

I have been working to redesign our spoilage tracking because departments have been sending defects downstream in the process and we need to catch this. Previously, we measured spoilage at the department. However, printing can easily have color variation (a defect) and send the sheet downstream. When it is discovered in a later process and removed, the department that discovered is being credited with the spoilage.

My redesign allows downstream processes to credit spoilage to previous departments. So, if a carton is found to have color variation after it has been cut, then it is measured differently but is the same defect as if it were discovered by printing.

Does this make sense?

 

[Andrew Fergus]

Yes this makes sense.

A 'Colour Defect' would be one of the entries in the 'Defects' table. When a defect is discovered, I would expect to record that defect in the 'readings/defects' table, along with the order number and the quantity of defects, and I would (possibly incorrectly) assume the defective item would be physically removed from the entire process. By removing the defective items from the process, the detected defect would only be recorded once, in other words, you wouldn't record the same 10 defective items through say 6 consecutive processes.

However, it is entirely possible that the department that discovered the defect may have missed some and that additional 'Colour Defects' could be detected by a department later on in the process. So, we could have multiple instances of the same defect on the same order. If this is the case then a couple of changes are required to the tables. In your readings/defects table, add an autonumber field to provide you with a unique key (this may come in handy later). Also, you have stated that differing departments have differing units of measure.

We could introduce a department table with a unit of measure field and link that to the readings/defects table, but that may exponentially complicate matters later on (given differing defects have differing units of measure in differing departments, we may not want to go there). An alternative approach, as you identified earlier, would be to record the unit of measure in the readings/defects table and remove it from the defects table. I would also recommend adding a department field into the Readings/Defects table so that you have an electronic record of who detected the defect. ( <- that table name is now a complete misnomer but I keep using it for consistency purposes)

Sorry for having confused matters but I trust we are now talking about the same thing and this clears things up?

Andrew

 

[dkubiak]

This certainly clears things up and we are now discussing the same thing. All that you have written makes sense and I have it drawn out on paper.

I plan to move the unit of measure field to the readings/defects table, as we discussed. The only other difficulty is varying weights and sizes of our products. We produce boxes for a variety of consumer products. So, depending on customer orders/requirements, we have a variety of goods moving through our facility. One customer's box is heavier than another's. Simply indicating unit of measure as pounds or inches will not work.

I propose the following: indicate unit of measure in the readings/defects table as inches, pounds, et cetera. Create a seperate table with Order Id, box thickness, box weight. This table could then be used in conjuction with the readings/defects table to generate an accurate count. Does that sound right?

 

[dkubiak]

I actually could create a table much like our Defect Table that lists all of the measurements of our various products. We only introduce new products about 2-4 times per month. I'll create a Measurement Standards Table withe a Measurement ID, Description, Weight, Thickness, et cetera...

 

[Andrew Fergus]

If you only have one item per order, then you could record the size and weight in the order table itself, rather than creating a new table [Late edit :, or as you suggested create a table of standard measurements.]

However, there are probably going to be multiple items per order (e.g. 100 of product x and say 1m of product y on the same order) so the readings/defects table should really link to the line item of the order instead of the order itself. To do this you need an order_item table (with a unique id of it's own, which would be the link back to the readings/defects table) plus it would link to the orders table using the order id. This table would contain all of the products for each order. Meanwhile, the order table has the customer details, date etc.

There are a couple of ways of recording the size and weight, depending on whether the products only come in standard sizes and weights, or if they are custom made.

If the products are all standardised (i.e. the customer selects from a pre-defined list of box sizes and the customisation only involves external printing and other cosmetic changes) then you should have a table of products and in there you could record the size and weight for each type of box. The order_items table would link to the products table via a product id and given the readings/defects table is also linked to order_items then you have a link in your data and you can do the standardisation calculation in your query.

However, if the products are highly customised, then you should record the size and weight etc in the order_items table (rather than creating a new product for each and every possible size and weight of box) against the product ordered by the customer, but this would occur in the order_items table, not the order table. This sort of set-up is almost exactly what you proposed in your [Late edit : second to] last post, except I have expanded it to take account of multiple items per order.

HTH, Andrew

[Edit : P.S. It looks like you beat me to it - you answered your own question while I was typing my response (and saying goodnight to my son), but you may still need to look at the multiple items per order scenario ]

 

[dkubiak]

Our product is highly customized; however, customers tend to order the same box in either very large quantities or on a regular basis. The only that changes regularly is graphics, and that will not affect measurement. Also, there is only one unique item per order.

With that said, I think my later idea of having a table of Standard Measurements will work fine. As we develop new products for our customers, I'll just add them to that table.

Thanks for all of your help.

-Dan

 

[Andrew Fergus]

You're welcome, and all this from such an innocent opening quesiton about table sizes!