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 🙂