Database Design for Order management for multiple sources

Question

I would like to know what's the best method for building an order management database where there are multiple shops, each with their own list of products and prices.

My Current Design

Product
------
name
image
price_per_item


Shop
------
name
product_list - Manytomany field 


Order
------
order_id    primary key
shop 


Order Content
------
order_id    foreign key to Order
product
quantity

The problem I foresee with this is that each shop may have the same item but may sell that item at a different price_per_item.

How exactly do I build a db where Shops can manage to have different price per items and yet have multiple items to sell?

Answer 1

You're too worried about the relationships, and not worried enough about the tables.

First, let's build a Shop table.

Shop
----
Shop ID
Shop Number
Shop Name
Shop Address
...

Generally, tables have a singular name and an auto-incrementing integer as the primary key. This is also called a blind key. That's why I have a Shop ID and a Shop Number. The Shop ID is the primary key and the Shop Number is data. The Shop Number can be changed without changing any of the relationships we're going to build.

Now, let's build a Product table.

Product
-------
Product ID
Product Name
Product Manufacturer ID
...

The Product ID is the primary key. The Product Manufacturer ID is a foreign key to a Product Manufacturer table. I'll leave the creation of the Product Manufacturer table as an exercise for you.

Now, a shop can have many products, and a product can be carried in many shops. This is a many-to-many relationship. We model this relationship with a junction table.

ShopProduct
-----------
Shop ID
Product ID
Product Cost
Product Price
Product Count
...

The primary key is (Shop ID, Product ID). You also have a unique index on (Product ID, Shop ID). The Product Price and Product Count values belong to the many-to-many relationship, not the Shop or the Product.

You can get a list of the products for a shop through the primary key. You can also get a list of the shops for a product, and the total inventory as well as average price through the unique index.

Now, we can talk about orders. You have the problem that an order can only go to one manufacturer. You also want to combine orders from the stores so you can order the correct quantities for all your stores.

Let's build the Order table.

Order
-----
Order ID
Product Manufacturer ID
Order Time Stamp
Order Delivery Date
...

The Order ID is the primary key. The Product Manufacturer ID is a foreign key.

Next, let's build the OrderItem table. This is a standard name for the items in a order.

OrderItem
---------
OrderItem ID
Order ID
Store ID
Product ID
...

The OrderItem ID is the primary key. The Order ID is a foreign key. The Store ID is a foreign key. The Product ID is a foreign key.

You get the manufacturer information from the Product Manufacturer ID in the Order table, using the Order ID.

You get the order information from the Order ID

You get the store information from the Store ID. This information is not necessary for the order. It is necessary to know where to deliver the products after the manufacturer ships the products.

You get the product information from the Product ID.

I hope this is enough of an explanation to get you started. First, you model the objects, like Store and Product. Then, you model the relationships between the objects, like ShopProduct.