Way back in the early days of the Internet, before WordPress and Shopify, a client asked me to build an e-commerce website with inventory control and a whole range of solutions.
These specifications grew over time as his business grew. Everytime we made something easier he would ask me to streamline another process.
- Build a Website to handle large number of customers
- Build a custom shopping cart - address verification
- Track Orders
- Scan products as we fill orders
- Handle inventory in the same database as the website
- Handle assemblies (products built out of other products)
- Handle product processing (sent out for processing)
- Handle Purchase Orders
- Track coupons by category, product, shipping, etc.
- Barcode everything!
- Make it easy for any employee to build an assembly
- Make packaging consistent, even for novice employees
These are advanced features, even by today's standards.
How did I do this?
Actually, I built this while maintaining dozens of other customers, running a company and building my own CMS. Yes, I was busy.
MySQL with InnoDB
For data consistency I chose InnoDB for it's transaction support. I also used it's database trigger and procedure capabilities.
Triggers are great but limited
Triggers help make data within a record uniform. However their greatest limitation is there inability to update records in the same table. Many database designers intentionally add this limitation to prevent infinite loops.
Assemblies are products in inventory constructed from other products in inventory.
This looping back to itself causes lots of problems with infinite looping if a product refers to itself at any time.
Assemblies make everything an order of magnitude more difficult.