Modeling the relationship of the order to the product in the entity structure

Question

This is not a technical question, but the question of how to model the data.

First, I use the Entity Framework to create a data warehouse.

I am creating an e-commerce site for small businesses, and I created a system that seems to be working fine, but now I realized that I made a fundamental mistake (I think!).

I have an order object and a product object. The product obviously contains all the product information, including price.

Now I have created over 100 products that are now in a table called Products

Therefore, when I create an order, one product or several products is added to the order. Thus, the order now contains links to the product table.

This works fine until I realized that I can change the price (provide a discount, etc.) of the product in the order. Since the product in the order is tied to the product table, if I make changes to the price, this will change the price for all previous and future orders.

So it looks like I need 2 product tables. One of them is the current list of products for sale, and the other is the goods sold, which are children of the order object.

What is the best way to simulate this? I am sure that I will need 2 different product tables.

Any advice on this would be greatly appreciated.

Answer 1

Why not create an order item object that captures the actual price paid? This is a pretty standard way to do it this way.

Answer 2

There are several ways to achieve this goal: exactly what you want to achieve, and complexity will determine which solution is best for you.

Personally, I would not have a direct link to my order (if at all) for the product. My order will consist of order lines that will contain a description, unit price, quantity, etc. If you want to enable navigation or a link to a product, I would add a link to the product in the order line. The price of the product is never included in the calculation of the order value, it is simply copied to the order line at the time of order with any discounts / modifications that also apply. Then it would be safe to change order lines without affecting any product or other order. An order line will always refer to only one order.

I assume that the relationship between product and order is a lot for many? If this is the case in the link table (which may mean that you need to manually specify the intersection table in the root code of the entity), you can add the property of price conversion. It can be just a percentage - 100% unchanged 90% for a 10% discount, etc.

You can completely separate the prices from your product, then you can assign a specific price to a product to a specific user, group of users or even the quantity ordered, depending on how difficult it is. This is likely to prove a lot of work and does not look like what you need.

Also, although I probably wouldn’t want to do this, you could make each product record unchanged (it will never change) and the changes will be modeled by creating a new record and then marking either the original or newly created product as " hidden "(due to lack of a better term) and generally not available. In the scenario of providing a discount on a product, you must change the product associated with the order, and behind the scenes what it really does is create a new “hidden” product with a new price on it. You will need to process this on the page of your product review in order to determine the status of the product and process it accordingly. If the order history page is used and allows navigation to the product for which you have provided a substantial one-time discount, you want to make sure that your application does not allow another order at this price.

Answer 3

You can have tables / entities as follows:

Product (ProductId, ProductName, ProductDescription, Price, ...) Order (OrderId, DateTime, ...) OrderItem (OrderItemId, OrderId, ProductId, Price, Quantity, ...) 

If necessary, you can also have a stock table.

Answer 4

For your solution, I would do the following model

  public class Product { public int Id { get; set; } public double Price { get; set; } public bool Archived { get; set; } } public class OrderProduct { public int Id { get; set; } public Order Order { get; set; } public Product Product { get; set; } public double Price { get; set; } public int Total { get; set; } } public class Order { public int Id { get; set; } public int UserId { get; set; } public List<OrderProduct> Products { get; set; } public Discount Discount { get; set; } public DateTime CreatedAt { get; set; } } public class Discount { public int Id { get; set; } public DiscountType Type { get; set; } public string Code { get; set; } public double Amount { get; set; } public DateTime StartDate { get; set; } public DateTime EndDate { get; set; } public List<Product> ProductsToApply { get; set; } public List<Product> RequiredProducts { get; set; } } public enum DiscountType { Amount, Percent } 

Product - I think no need to explain

Discount . If you need to lower the price, then yes, you must change it to a product. But at a discount you are more flexible, since you can create limited time offers only for specific products, you can also add the necessary products for purchase to get a discount But in order to solve the main problems, I would do it in two tables, and OrderProduct . There I would save how many products the buyer bought and their price.

Order . If a customer has bought products, you create an order and add OrderProducts to it with reference to the product and its current price. Also for the order you will attach a discount if any

Answer 5

There is no need, but there is redundancy to have two product tables. You need an additional field in the order table, indicating the sold unit price of each product. And at the end of the day, when you want to calculate your cash flow, you must indicate the unit price set in the order table.

For further development, you may need a history table for product prices. So you can take a peek into history to analyze price adjustments.

Answer 6

I had a similar situation last month when I was working on an electronic website. I used a similar approach (as Rob West and Allan suggested), where each order will have several OrderItems. Each OrderItem will have a link to the Product, but for it it will have two more fields: ItemCost, Discount, SubTotal.

When creating an order, I will copy the product price into OrderItem, and then apply the discount.

In addition, I had a GroupDiscount column in the order table that would allow me to offer a collective discount on the entire order.

This works great in production without any problems.

Answer 7

Usually you make a table called OrderProduct or whatever you want to name, then this table will have everything that needs to be frozen in time as soon as the order is placed. Your objects at the end will look like this:

 public class Product { public int Id {get; set;} public string ProductName {get; set;} public virtual ICollection<OrderProduct> OrderProducts {get; set;} } public class Order { public int Id {get; set;} public virtual ICollection<OrderProduct> OrderProducts {get; set;} } public class OrderProduct { public int Id {get; set;} public virtual Order Order {get; set;} public virtual Product Product {get; set;}//this is optional, //you would only include it if you need to query //other information about the product //that wouldn't change (color, size, type, etc.) } 

Answer 8

You can use a different approach. Try saving prices (and change history) in a separate table.

In short, create a ProductPrice table (ProductId, Price, StartDate) and use it for all price changes. OrderItem should reference ProductPrice instead of Product. For each new order, get the latest price (by StartDate).

For custom OrderItem orders (discount for vip client, additional delivery costs, etc.), create fields in the OrderItem table. If there are many different factors that influence the price, create a separate table. For example, PriceModification (list of modification rules) and PriceModificationInOrderItem.

For more information, see Creating Historical Tables in Relational Theory. For example, this article: http://database-programmer.blogspot.ru/2008/07/history-tables.html

Answer 9

Just add the PricePaid field to the PricePaid table. Prices may vary, but orders remain.