Redesigning my retail software/database - part 2
-
After the tax issues last year and this year, I decided it was probably time to redesign my software from the ground up before 2020 is over. The system as it stands is basically seven tables:
MacroInventory - orders and fixed expenses
MicroInventory - inventory items
MacroSales - would've been order level stuff, like shipping and tracking, but never ended up being used due to rare multiple item sales
MicroSales - sales items, plus higher-level stuff like shipping and tracking
eBayFees - fee breakdown (manually carried over to MicroSales)
eCraterFees - fee breakdown (manually carried over to MicroSales)
Rules - basically notes for how some calculations work due to weird vendor behaviorWe're in part two, where, so far, there haven't been too many changes. The old table has 17 columns. The new table has 26 columns. Only two really affect math functions; the rest are just for better records.
- Legacy(smallint) - links back to the old ID column
- Box(tinyint) - secondary locator component carried over from new order table
- Active(bit) - status carried over from the old order table
- Preorder/FireSale(bit) - carried over from the new order table, reduced states allow for two vs four
- Exclusive(varchar) - store/channel item was exclusive to (if any)
- Subline/Variant/Number(varchar) - mostly for differentiation
Most of the effort so far has been filling in those last three and the old description field. I had alot of listings that made sense when I started but became confusing as time went on. For example, there are listings for "Marvel Legends Black Widow." At one time, there were only two Black Widow figures out. Now there are a dozen. So what I've been doing is making sure it's as clear as possible what version is listed where.
I think that'll be a big help in the long run as I work down through inventory. That nightmare went from bad to worse between 2018 and 2019. It will be a challenge figuring out some of the phantom/mystery items that caused me problems the last two years.
I'll also be adding some triggers this time. At present, I'm filling in data at a large offset. Once I have everything in, I'll have to manually push it back into the real range. The triggers should help keep some of the links together. One, items ordered by the case have a link to a case record that needs to be there so they're not double counted. Two, sales references need to move if item references do.
So far, I've got about 7% of the orders and 1% of the items back in the system, not counting stuff that's new for 2020. That should pick up a little over the next few days.
-
@Zenith said in Redesigning my retail software/database - part 2:
That nightmare went from bad to worse between 2018 and 2019.
I've got bad news about 2020…
-
@dkf said in Redesigning my retail software/database - part 2:
@Zenith said in Redesigning my retail software/database - part 2:
That nightmare went from bad to worse between 2018 and 2019.
I've got bad news about 2020…
Oh, I know it's going to be awful from a business standpoint. I would be somewhat surprised if sales broke into four digit territory. At this point I'm somewhere in the ~$800 range. On the bright side, that's more than I sold online* last year. It also might be more than I spent on inventory acquisition but that's a little hazier at the moment.
Still, per the topic, I think I'll have worked out most of the software problems for awhile. I don't see any major new quirks to the business model on the horizon. I've been formulating some more ways to verify the data but they don't make sense to implement until more of that data is available. That'll probably be part 4 if I get to it.
Edit: A side goal for 2020 is to balance some accounts. Back when business was much better, the plan was to shift inventory purchases from my money to business money. eBay was doing pretty well and I had a few vendors that used PayPal, so I decided to start with that account. Unfortunately, sales sort of collapsed and preorders outstripped my buffer, so I had to transfer money in from other accounts.
I've been wanting to repay those "loans" for some time but it's sort of complicated. I figured out how to repay them exactly (awfully slow view because of so many joins), including missed interest, so it'll look like it was never borrowed. The easiest way to do that, though, was to make one lump payment per account, which meant saving up significant eBay sales. As I said, eBay sale shave been in full-on collapse for a few years, so it's been slow going.
I estimate that I have between 1/2 and 2/3 of the funds necessary. Mangled Payments is coming by the end of the year, which has been my deadline for exiting eBay. Five months is probably not enough time but maybe I'll make it. The shutdown may have wrecked business overall but it has marginally helped online sales so who knows.
