I’m creating an app that allows users to post, sell, and purchase items. Every item has a count integer attribute that is set by the initial user and which is reduced by n with each purchase. When count is 0, that item can no longer be sold unless one of the previous user buyers reposts the item they purchased: in which case, the new item inherits all the properties of the original item except for price, which is determined by the new user owner, and count, which is determined by how much count they purchased in the initial transaction.
I have two database columns at the moment – items and users. I’ll need to record transactions and ownership transfers for as long as an item exists on the site, so this means that I’ll need a third column for transactions. What else would I need, and what else should I research? Is Standard Table Inheritance useful for this kind of functionality?
Should I scrap the whole count approach? Would it be a huge drag on the database if I created one new object for each [i] in count? So say a user sets count to 10,000 and I had the app create that many individual database objects for every item posted. And then say there are thousands of people doing the same thing at the same time with their own items.
Very new to working with database design and not sure where else to ask, so I really appreciate your feedback.
2
This is a pretty typical inventory management problem.
How you manage it depends on how you physically manage inventory. If each item (not SKU, but item) has a unique serial number, then you may be tracking specific item instances.
More likely, you’ll get a box of the item (widgets, say) and increment the inventory on hand by the count in the box, maybe 24. Each sale transaction decrements the inventory on hand by the number sold.
My rule is: keep it simple, but keep it real. If your have multiple warehouses in different timezones, or that kind of thing, you have a more complicated problem. If you have a single warehouse, then life is easier — just add num_on_hand or similar to the item entity. (Don’t use count since that’s a SQL keyword, amongst other reasons).
This is a good case in RoR for a transaction; make the decrementing of num_on_hand part of the add to cart or checkout transaction. Don’t forget things like adjustments (e.g. end of year inventory), breakage, returns, and all the other things that are transactions but may or may not actually change inventory.
I would also hasten to point out that this is a problem that has been solved countless times, and is just one of many issues around content management, store management, etc. Is this software you really want to write yourself, or is there a nice existing gem or tool that you can use to manage this complex problem more simply so that your company can get to the problem of doing what it’s good at, like, selling stuff 🙂