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.