Database schema for an online shop

Question

So I'm fiddling with how you could design the basic db schema for a webshop. Sure, there's a lot of missing details here and there, but I just want to get the most basic stuff right.

Some basic requirements for the shop, which I'm trying to make a schema of:

The shop has an inventory of products. Each product has a price, but this price should vary depending on sales. Customers can make orders for multiple products at a time, and should be able to see their order history. When the order has been completed, there should be a track and trace number.

So, the following image is what I've come up with so far. I'm sure something is missing, I'm just not really sure what, also how to model the dynamic price aspect.

Solution 1

A common way to handle variable price would be to introduce a rebate schema. This can be implemented as association class between Product and Sale by adding some rebate to amountSold. Of course there are many different ways, but this is one at hand.

• I'm missing a relation between Order and Customer.
• Not sure what you intend with the Inventory aggregating ProductList. To me a Inventory lists Product. There might be the need to a store location and a PurchasePipe

Solution 2

I think this is the according to your question give a flow of it how to create a schema and Additional that Link here please visit once you will find some amazing part.

Comments

[Left4Cookies]

A rebate schema makes sense. What kind of relation are you missing between Order and Customer? You are right with the ProductList. I've removed that one, since an inventory is, well, a kind of product list.

[qwerty_so]

Sorry, I meant the Order lacks a Product FK and the amount.

[PeakGen]

One of the best I have seen.

[Alwani Anis]

Where can I find explanation of this?