Level 75
Have you also searched github for similar software, many times you can get ideas of how it's accomplished there. Are they using barcodes, or doing things manually.
Aug 14, 2020
5
Level 9
Online store inventory
If I was going to create a website for a supermarket where a person can purchase online, as someone who has never made a website like this how should I approach the inventory part. Should I make it from scratch as something real simple like a table that just stores the product and the current amount and be able to manually add more stock by updating the amount, or should I use a package and if so which one do you recommend?
This is for a small family store so the actual inventory doesn't need to too complex, some of the packages I've seen seen to be for a big store or company and are very complex, personally I don't know if that is the best route for a small store, this is like a mini mart store.
I had thought of having the db something like this
//Tables
products (id, name, image, price)
categories (id, name)
category_product (category_id, product_id)
inventory (id, product_id, amount, last_purchase_date)
cart_products (user_id, product_id, purchase_amount)
sales (id, user_id, cart_products_id)
users (id, username, password)
Also how can I handle the my cart logic, is it best for the user to be logged it in order to add products to their cart or should I allow the person to add stuff to the cart while not being logged in?
I know these are a lot of questions but any tips or tricks are greatly appreciated.
Level 80
@msslgomez I’ve worked on a similar project. Each product sold would be a SKU and have a corresponding barcode. Here in the UK, it’s an EAN (European article number), and usually 13 digits long. So your SKUs would form your catalog.
For each SKU, you could maintain a table of stock level adjustments. When stock comes in for a particular SKU, record the quantity. When a customer purchases a SKU, create an adjustment with a negative number. Your stock level is the sum of the adjustments for that particular SKU. Create a compound index on the SKU foreign key and quantity column, and you’ll be fine if it’s a small store and not generating billions of rows like say, WalMart would.
Level 9
@jlrdw the person I'm making this for is pretty clueless as to what they want to do, and well I've never made a ecommerce website with an inventory so I'm not sure what to suggest. Is using a barcode better ?
Level 9
@martinbean so the products table would have an id, sku, and barcode? I'm not sure I follow the stock level adjustemnts part, so that is a different table and what would go there? the product sku and the total quantity of items coming in? When a product is bought the quantity they purchased would be subtracted from the total items. Do you suggest using this as the inventory and just display it as a table?
Level 9
@martinbean Could you please elaborate on the inventory table a bit, I'm at this stage in the website and I'm a little confused. This is the table I have so far, per your suggestions above
Schema::create('inventory', function(Blueprint $table) {
$table->index(['product_barcode', 'quantity']);
$table->unsignedInteger('product_barcode');
$table->integer('quantity');
$table->foreign('product_barcode')->references('barcode')->on('products');
});
This gives me an error, and I can't figure out why
General error: 1005 Can't create table
arthurs.inventory(errno: 150 "Foreign key constraint is incorrectly formed")")
You suggested a compound index, could you elaborate why? and how I'm supposed to use it. Thanks
Please or to participate in this conversation.