Zen-cart as an inventory management system?

[wolfderby]

So I need to get to 1.5.7, currently running a small biz on 1.5.6... but anyway... I've been using zen-cart as an inventory management system, by doing this w/ the product models...

PRODUCT1-001 < first time I ordered "product1"
PRODUCT1-002 < second time I ordered "product1"

then I have a custom "invoice field" w/ poor data atomization (just didn't know better at the time of creating the field) where I do...

060121VendorName002-003 < date of invoice (June 1st 2021), Vendor Name, 002 = 2nd time ordering from that vendor, 003 = 3rd item on the invoice

...then I have the product cost add-on (https://picaflor-azul.com/profit-mar...en-cart-plugin)

ANYWAY, my question is... "what's the proper relational database sort of way to manage re-purchases?"

Should I make a table that's `PRODUCTS_VARIATIONS` ... and have a foreign key that links the invoice and cost data?

I currently have a products table that simply gets bigger with each order, would it be significantly worth it to try to re-configure w/ this relational "variations" table?

I like the idea of building up one listing for "PRODUCT1" and then re-using it, but it seems like it might be an enormous undertaking. Like say one customer's order has 2 qty of PRODUCT1 and 1 comes from invoice1 and 1 comes from invoice2...

Anyway any thoughts... suggested googling, etc would be appreciated.

I had also messed w/ upgrading a quickbooks desktop sync add-on a while back by just googling error codes before I understood what objects were as a data-type... this I mention because I could possibly try to use that to manage the inventory.