Basics of Shopping Store and its schema [closed]

Question

I am planning on making a generic shopping store website like a smaller version of alibaba.com which could be used by different businesses to list their product in their own version of the shopping store.

Before starting the project I want to be sure about what I am going to do. One thing which I want to ask here is this :

---

1) Does the shopping store keep track of all the inventory with regards to its original cost of products as well as selling price of product both, or only the latter one? If it keeps track of both then what is the schema going to be like if a particular product is entered in the inventory at different rates at different times for example 100 items of Product1 costing 100$ and priced at 150$ on date1 & on date2 Product1 is again added to the inventory costing 120$ this time and priced at 150$ like before. How am I going to store that information in the database and make calculations of profit and Cost of goods sold(COGS) in this scenario. I am slightly aware of FIFO LIFO inventory management but I am not sure is it going to be a part of the shopping store or is this not the responsibility of the shopping store to keep track of original cost of goods sold vs the profit. If it is, then what should be the schema of the database be like to accomodate these requirements.

---

2) In typical shopping stores, a single product has different attributes & the shopping store is aware of the current quantity of a product with particular attribute. For example A T-shirt with productId=1 may come in two different colours and the inventory has 10 blue coloured tshirts & 5 black coloured tshirts with productId=1 & attributeId=1 & 2 respectively. How am I going to keep track of the inventory of a same product with differet attributes. I mean there is only one Product table in the database, How is the schema going to accomodate these requirements that the inventory knows about the quantity of different attributed-products with the same productId.

---

I googled alot before posting a question here. I came acros some database schemas of shopping store, but I am not sure how do they manage the inventory keeping in mind my two above points. Please have a look at it & tell me what would the roadmap be like. And lastly I do want to make my own shopping store & not use prebuilt shopping stores just for learning purposes as well as some other reasons. I am going to use ASP.Net MVC with sqlserver.

Please see attached image of shopping store schemas I found on google.

How am I going to track & maintain inventory if I use a somewhat smaller version of the schema shown in the picture. Where do they specify the quantity of the products becuase the Product table doesnt have it. Also how would I maintain the quantity,cost & price of same product with different attributes.

Thanks.

Contented

Answer 1

1) Inventory always reflects the current price. There should be a shopping cart that holds the in progress orders - AND a separate order items table that holds the items for a specific order. the prices in the order items table does not change because it is associated with a specific order. so you have the current inventory with the current pricing AND you have a record of the orders and what was sold.

versus the prices in the cart which should change because they should reflect the current pricing of the product. the price for the product is frozen when they complete the transaction - NOT when they put the item in the cart.

in the same way Inventory is not reduced when someone puts something in the cart - its reduced when the transaction goes through and the item is actually sold.

2) In the larger world of commerce every 'buyable' product has a unique UPC code or EAN code. And the merchant may have their own SKU they use for inventory. And the e-commerce system may have its own ID for each product. Look at it this way - if there is separate stock for the product, there has to be a separate ID in order to track that stock. If a shirt has 3 sizes and 2 colors - that equals 6 different products for inventory that are sitting on shelves.

even though there will just be one web page for the shirt - somehow the merchant has to know how many of each specific size & color there are in stock. so one description of this is called Parent Product - Child Product. the Parent Product is "the Shirt" - the description, the pictures, the page, the reviews, etc etc. The Child Products are the actual buyable items. Amazon seller central has some good descriptions of this.

my suggestion is to create your system so that the cart is not in control of price or inventory. there is a products database which is current and your e-commerce system is constantly checking. you store the product id in the cart, and then with any cart refresh and at checkout - you are getting the price from the products table and checking inventory. VERSUS passing a price from the product page to your cart and never checking it again.

Lawgiver

Thanks for the detailed response. I get the second point. However in my first point I meant something else. I meant, that If x quantity of Product1 is added at one point in time for $10 AND after some time y quantity of the same Product1 is added at a later date for $20 this time BY THE SYSTEM ADMIN NOT THE CUSTOMER PURCHASING it i.e the owner of the sotre adds same inventory items at different time for different costing price BUT same selling price, How am I going to handle that? – Contented

thats a great question - take a look at established retail point of sale software as well. another way to look at it -- whatever is feeding the data -- to really come up with COGS is going to be done in accounting software. I'm sure there are systems that will run reports, but for most companies that data is still going into stand alone accounting software that is the final word in running the business. – Lawgiver

should the "products" table for an ecommerce website have two columns: "availability" and "quantity", so that when user clicks on checkout, availability will reduce, and when the order is completed, quantity will reduce? or should it just be "quantity" ? – Ascension