CODEWITHSUNDEEP
mysqle-commercedatabase-schema
Ninad Ajnikar
Ninad Ajnikar

Reputation: 183

E-Commerce Database Design for Orders Table

I am designing a schema for E-Commerce app, in which I would have 3 tables i.e Orders, Products, Customers.

So Should we store customer_id and product_id in Orders table straightaway.

The limitation to this is when a product or customer updates their attributes( i.e product price or customer name ), the orders table doesn't reflect them.

For Ex: A Customer bought a product at $10, but later on the product price gets updated to $20.So now when we are referring to this order by product id we would get the result as it was bought at $20 instead of $10.

SOLUTION 1:

One solution would be to insert a new row into products table whenever an updates occur and perform a soft delete to that product so that it can be referenced from orders table.

SOLUTION 2:

Store most of the details in product and customer details in orders table.

SOLUTION 3:

Create a temporary table of customers and products whenever there is an update to these tables.

I am very much open to any other suggestions.

Upvotes: 1

Views: 7559

Answers (1)

Brian Hoover
Brian Hoover

Reputation: 7991

One thing that you seem to be missing is a orderLineItem table for anything other than the most simple solution, where there is one product/order.

Now, that being said, you can do the products table in several ways.

Assuming that price is your only variable in the products table that you want to change, you can have a separate pricePoints table, that would store the price for any item at any given time. You would then use the ID from this table in your orders table and use that to get to the productID from the products table. A slightly more inefficient way to store this (but faster for retrieval) would be to store both the productId and the pricePointId in the orders table.

You could also do this by simply storing the price paid amount in the orders table. This gives you a little more flexibility to add discounts and pricing rules. You do need to be concerned about auditing the price though if you do it this way. Why was this price charged for this line at this time is going to be a common question.

You need to know how much the customer paid for the product at any time. It's not so important to know how much the customer would have paid for the order if they bought it today.

Customers are a slightly different issue. Some of the information in a customer table is transient. Some of it has to be fixed for the order. Lets say that the customer has a name, address, billing address and shipping address. At the time of the order, the shipping and billing addresses have to be absolutely fixed. You don't want to go back in three weeks and discover that the shipping address was changed. But, by the same token, you might like the name to be updated if a customer changes their maiden name, for example.

Now, all that being said, we aren't going to design your schema for you. There are a lot of good resources out there for how to design a simple e-commerce database.

Upvotes: 2

Related Questions