So my client has a database of returned (bad, faulty) parts. Each has a part number, came out of a vehicle with a specific vehicle ID (VIN), which in turn has a model and make, and that vehicle is ON a specific lot out of several different car lots.
Client has been bugging me for 2 days to add a “Failure RATE %” field which, says the client, should be computed by summing up the number of records that share the same part number + the same make and model and are on the same lot; and then DIVIDE that number by the total number of cars of that make and model on that lot.
I keep saying “But that doesn’t make any sense”. Client keeps insisting that of course it does.
Is it me that’s having the brainfart? Well, hey, the customer is always right, when the customer is paying and yeah I can do that mathematically. I can express the result as a percent.
Percent of WHAT?
Methinks the units in this equation just don’t parse. It’s not the percent of those parts that go bad, it’s not the percent of cars that have faulty parts. I don’t know WHAT the hell it’s supposed to represent. Might as well be cubic parts per model-lot squared for all it tells me.
Finally I ask: “So… imagine that it is now July 2016. About six years ago, way back in 2010, a few alternators ‘part xyz’ were returned on Toyota Corollas from Lot One. Over the years that has continued happening, a dead alterantor now and then. In 2016 you don’t have any more Toyota Corollas on Lot One than you did in 2010, but your failure rate is a lot higher. Eventually, given enough time, you will have more alternators returned than you have Toyota Corollas and your failure rate will be more than 100%. Tell me again what this number means?”
Were these returned parts sold as replacement parts? If they’re replacement parts it seems like you could track them by part number, number returned divided by number sold.
I guess the customer is trying to get a feel for what cars and/or parts are most reliable?
Is your concern that different makes and models will have the same part, or that he’s using a cumulative total of bad parts vs. a “right now” total of cars?
If the field (with a very different field definition) should exist anywhere it should exist in the table of Models, or perhaps a new join table representing the intersection of models and lots.
Many years ago I was asked to add a “13th month” to each year in the chronology table so that they could do their financial reconciliation there and ‘make their year come out even’. (Which would be a nasty mess since the “month” is determined by formula, the month OF the date field which in turn has to be a valid date thank you very much. I can’t input 13/23/2003 and expect the db to accept that. But anyway…)
“In other words”, I probed for clarification, “you want to make your estimated / budgeted expenditures come out to match your actual expenditures at the end of each year? So that it looks like you planned really well?”
“yeah!”
“I think I need to speak to legal about that first…”
The latter, in conjunction with the fact that what the @%#$@!@# does it mean to divide the cumulative total of bad parts (same part number, same model, same lot) by the total quantity of cars? Even were it NOT cumulative, what is the resulting percent supposed to be a percent OF?
If Part X on “Corollas” at Lot B is 13.3% while Part Y on “Tauruses” at Lot C is 7.9%, what the heck does that tell anyone? (Aside from the fact that neither Lot B nor Lot C has zero cars?)
It would mean to me that Part X goes bad in Corollas 13.3% of the time. But the lot wouldn’t matter, unless there is some pattern to how cars are assigned to lots. That’s probably not what it means, but that’s the way it would look to me.
I just had a patron come over to the desk after I’d explained to him how to find children’s books (alphabetical by author unless they have a Dewey number) to say that none of the books were in any kind of order.
I went over to the shelf with him and he pointed "Look! ‘Windmills’ then ‘Red’ then ‘Clifford’ … "
“The author is the person who wrote the book, not the title of the book, sir.”
After three explanations of how to place plants for display at a nursery, the owner started asking people at their first interview, for what was an unskilled labor job, “Are you familiar with the alphabet, and the traditional order in which the letters are arranged?”
I loved the look of confusion on the faces of the college kids working summer jobs there.
They want it on a per lot basis to see if someone is stealing, making significant errors, etc. when compared to other lots. If you’ve got 10 lots and in 1 lot the Really Expensive Widget in Numega Jalopies just happen to be consumed at a significantly higher rate, odds are you’ve got a thief or a bad mechanic - either of which would be fired.
I might be missing something here, but I don’t think the premise sounds entirely off the wall. If they want to classify and track the defective parts by makes and models, AND normalize the raw counts by the different sizes of the lots, I suppose they can attempt to make fair comparisons between the lots–how many defects reported, and so on. If the biggest lot in the enterprise has the smallest raw number of reported defects, then I’d think they’d want to know about that.
I think it is this or something akin to this, the more I think about it. That is, what was throwing me off is that I was trying to make the resulting number be “about the vehicle” or “about the bad part” and I couldn’t see what this statistic was telling us about either of those things. Aha, it’s about the LOT. Whether it’s a concern about stealing or something a bit more benign, what makes sense is that it tells us something about the lot.
