Building a Better Product Catalog
Theory
As much as I love ZenCart, it has limitations. One of the things I’ve never seen in an online shop system is an understanding of package deals. For example, I have kits that are made of parts. Every part is available (or should be available) in the store. The price of kits should change whenever I change a part price, and the quantities of parts should change whenever I sell a kit. ZenCart doesn’t understand either of these things so I’m left to do a lot of manual work. Call me lazy but I don’t like doing work if I can make the machine do it. As my good friend Steven says, “if you have to do it more than twice… automate it.” Happy 30th, Steven!
Details
So, where was I. Oh right! So I’m building a new database that has all my inventory in it. The mysql table looks a bit like this:
| Field | Type | Null | Key | Default |
| id | int(10) unsigned | NO | PRINULL | |
| SKU | varchar(32) | NO | PRINULL | |
| next_id | int(10) | NO | 0 | |
| prev_id | int(10) | NO | 0 | |
| name | varchar(64) | NO | NULL | |
| qty | float | NO | 0 | |
| supplier | text | YES | NULL | |
| weight | float | NO | 0 | |
| description | text | YES | NULL | |
| MSRP | float | NO | 0 | |
| bulk_purchase_qty | float | NO | 0 | |
| bulk_purchase_cost | float | NO | 0 | |
| bulk_import_fee | float | NO | 0 | |
| unit_cost | float | NO | 0 | |
| video | varchar(128) | NO | ||
| data_sheet | varchar(128) | NO | ||
| unit_purchase_cost | float | NO | 0 | |
| video_quality | enum(‘missing’,’poor’,’ok’,’exemplary’) | NO | missing | |
| image_quality | enum(‘missing’,’poor’,’ok’,’exemplary’) | NO | missing | |
| description_quality | enum(‘missing’,’poor’,’ok’,’exemplary’) | NO | missing | |
| data_sheet_quality | enum(‘missing’,’poor’,’ok’,’exemplary’) | NO | missing | |
| margin | float | NO | 0 | |
I also have a table that describes inventory as one-parent-to-many-children. So a single inventory item can actually be made of many other inventory items.
Opportunities
– every version of every item has a unique SKU code.
– If I change a part in an inventory item completely it will assign a new SKU and adjust the prev_id and next_id. It could automatically update descriptions to say “this product has been replaced with [hot new version]” or “This is a replacement for [old and busted version]”.
– When an inventory item is updated my catalog will automatically adjust the price.
– When I make a sale of a kit that has parts it will automatically adjust the product quantity of each part. It can then list parts and kits as out of stock if necessary.
– Because I will have accurate inventory control I can predict when parts will run out and order replacements just in time. Less money sitting on the shelves waiting for someone to buy.
– The system can track who got what version of what order, and it will be a lot easier to produce a Bill Of Materials (BOM) and cross-check it before each kit is sent. It also means if someone has a problem with an older model I can track info on that version. “Oh yeah, that was a big problem in the ’38 Mark IIs – they fall right out of the sky” or “Well, I see here that minion 24 signed the papers saying this kit was OK to ship on last Tuesday. I’ll be sure to have him… corrected.”
Costs
Time. I’ve written a lot of PHP in recent years so it’s no sweat to pull together the core systems. I’ve already got a LAMP server running so that’s basically free. So far I’ve imported my old excel sheets full of inventory into the new database.
This afternoon I’m going to play with Arduino joysticks (as promised) and tomorrow I’m going to copy in all the parent > child relationships and then build logic to control what happens when and how. All the while my 3D printer is making delta robot parts and hog drives for happy customers.
Friday I have a disgusting pool to clean, Saturday I don’t know yet, and Sunday is a 5k run in my neighborhood. Some time next week the camera I bought with Bitcoins will arrive and I can start to take new product photos, at last.
The work never stops here, and I wouldn’t want it to – I’m having too much fun!
