If I build a POS system using Ruby on Rails, how should I store product, sales, payment, and stock information in the database?
1 Like
I’d keep the POS schema fairly normalized and separate the transactional records from the current inventory state. A common structure would be products, orders, order_items, payments, and an inventory table or stock-movement table.
order_items should store the product, quantity, and the price actually charged at the time of sale, rather than relying on the product’s current price. For inventory, I’d prefer recording stock movements (purchases, sales, returns, adjustments) so you have an audit trail instead of only maintaining a single mutable quantity.
For payments, keeping them separate from orders also makes it easier to support multiple payment methods or split payments later. I’d also use database transactions when completing a sale so the order, payment, and inventory changes either succeed together or roll back together.
1 Like
I agree with this approach. Keeping transactional data separate from the current inventory state makes the POS system easier to maintain and scale. Storing the actual sale price in order_items is especially important because product prices can change over time.
I also like the idea of using stock movements for purchases, sales, returns, and adjustments. It provides a clear audit trail and makes inventory discrepancies much easier to investigate. Using database transactions for the sale process is another important point since it helps keep orders, payments, and inventory consistent if something fails.
Another thing to consider in your audit trail: products come and go.
If all you store is an SKU or a foreign key in the order_items, then you have no path back to “what was that product named?” It is tempting to have a place for everything, and everything in its place. But duplication is cheap, and will save future you.
A more general-purpose way to handle this is gems like Audited or PaperTrail, which allow you to save all kinds of ephemera as it happens. They have their own set of problems (associated models can get lost/out of sync, and trying to engineer your way out of that can be a cruel learning experience). What I have taken to doing is adding a text column (or JSON if you have it) and stuffing a JSON-encoded graph of associated attributes in there. You can’t query it (maybe you can, but I haven’t tried that hard), but you can read it back and populate a screen when someone comes by in a few years and wants to see “my orders”.
What you spend on data storage duplication will pay you back over time.
Walter
1 Like
That’s a great point! Keeping a snapshot of product details with each order makes a lot of sense, especially when products are deleted or updated later. I also like the idea of storing associated attributes as JSON to preserve the original order history. How do you handle schema changes when the product data structure evolves over time?
That’s a question I haven’t really thought about. Mostly the schema has been added to over time, not really horsed around with. So older order items might be missing some keys. Haven’t actually looked at what would happen then. Lonely operators all around, I guess!
Walter