I maintain a small database that prints out UPC labels and tracks serial numbers. It does an OK job at this, but the code is starting to get a little crusty.
I got a wild hair today and decided that I’d had enough with a really annoying update process. Here’s the table I was working with:
CREATE TABLE operational_part_number ( id INT AUTO_INCREMENT NOT NULL, operational_part_number VARCHAR(20) NOT NULL, style_id INT10) NOT NULL, drilling_set_id INT(10) NOT NULL, color_id INT(10) NOT NULL, is_front TINYINT(1) NOT NULL, has_slotted_holes TINYINT(1) NOT NULL, image_location VARCHAR(255), primary key (ID) );
So, for every drilling, color and style, I have to create a set of data to insert.
There were 400+ rows in this table, and I’d just been asked to add another 100 rows. Of course, the GUI doesn’t include any admin support. So, I do this with a spreadsheet and awk every time.
Today, I ran this query:
SELECT LEFT(operational_part_number, 3) AS result, style_id, is_front, has_slotted_holes FROM operational_part_number_old GROUP BY result;
The result was 12 rows. I discovered that the operational_part_number was made up of three things: a prefix, the drilling_set, and the color. And there were only 12 prefixes.
Some rework was in order:
CREATE TABLE opn_prefix ( opn_prefix VARCHAR(20) NOT NULL, style_id INT(10) NOT NULL, is_front TINYINT(1) NOT NULL, has_slotted_holes TINYINT(1) NOT NULL, image_location VARCHAR(255), primary key (opn_prefix) );
Ahhh. That’s better. Now, the magic comes in with a VIEW to make the web front-end happy:
CREATE or REPLACE VIEW operational_part_number AS SELECT CONCAT( opn_prefix.opn_prefix,drilling_set.holes_count, color.code ) AS operational_part_number, style_id, drilling_set.id AS drilling_set_id, color.id AS color_id, is_front, has_slotted_holes, image_location FROM opn_prefix, drilling_set, color;
I put that all in place, and everything worked.
The end result was that I added only one row to the opn_prefix table, and future updates to part numbers will be just as easy. Tomorrow I am tackling the finished goods part number mapping to UPCs. I’m not sure I’ll be able to automate the process as well.