Modelling an Order to Product Relationship in Entity Framework

Question

This is less a technical question but rather a question about how to model data.

Firstly, I'm using Entity Framework to build the data store.

I've been creating a e-commerce site for a small business and I've created a system that seems to work OK but I've now realised I've made a fundamental mistake (I think!).

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

I have now created over 100 products which now sit in a table called Products

So when I create an order, a single product or multiple products are added to the order. So the order now contains references to the products table.

This works fine until I realised I may want to alter the price (provide a discount, etc) of the product in the order. As the product in the order is tied to the products table, if I make a change to the price, it would change the price for all previous and forthcoming orders.

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

What is the best way to model this? I'm sure I'll need 2 different product tables.

Any guidance on this would be greatly appreciated.

Answer 1

Why not create an order item entity that captures the actual price paid? It is fairly standard to do it that way.

Answer 2

There are multiple ways you could achieve this exactly what you want to achieve vs complexity will dictate which solution is best for you.

Personally I would not have my order link directly (if at all) to the product. My Order would consist of order lines which would contain a description, unit price, quantity etc. If you wanted to enable a navigation or link back to the product then I would add a link to the product on the order line. The price of the product never comes into the calculation of the order cost, it is simply copied to the order line at the time of ordering with any discounts/modifications also applied. You would be safe to modify the order lines then without affecting any product or other order. The order line would only ever relate to a single order.

I am assuming that the link between product and order is a many to many? If so on the link table (which may mean you have to manually specify an intersect table in entity framework code first) you could add a price transform property. This could just be a percentage - 100% for no changes 90% for a 10% discount etc

You could completely separate pricing from your product, you could then assign a given price to a product for a particular user, user group or even quantity ordered depending on how complex you want to make it. This would likely prove a lot of work however and doesn't sound like something you would be needing.

Also, although I probably wouldn't like to do this, you could make every product entry immutable (it would never change) and changes would be simulated by creating a new record and then marking either the original or the newly created product as "hidden" (for lack of a better term) and not generally accessible. In the scenario of giving a product discount, you would modify a product linked to an order and behind the scenes what it would really do is create a new "hidden" product with the new price on it. You would have to handle this on your view product page to detect the status of a product and handle it accordingly. If an order history page is used and allows navigation to the product you have given a substantial one time discount to, you want to make sure your application doesn't allow another order at that price.

Answer 3

You could have Tables/Entities as follows:

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

If needed, you could also have a Stock table.

Answer 4

For you solution i would do 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 needs to explain

Discount - If you need to decrease price then yes you should change it on product. But with discount you are more flaxible since you can create limited time offers only for specific products also you can add required products to buy to get discount But to solve you main issues i would do it in two tables , order and OrderProduct there i would save how many items customer bought and their price.

Order - If customer bought products then you create order and add OrderProducts to it with reference to product and his current price. Also to Order you attach Discount if Any

Answer 5

I had a similar situation last month when I was working on an ecomm website. I have used the similar approach (as suggested by Rob West and Allan) where with each Order would have multiple OrderItems. Each OrderItem will have a link to Product but apart for it, it will have 2 additional fields, ItemCost, Discount, SubTotal.

At the time of creation of order I will copy the price of Product into OrderItem and then will apply the discount.

Also, I had a GroupDiscount column on Order table which would allow me to offer a collective discount on the entire order.

This has been working fine in production without any issue.

Answer 6

There's no necessity but rather a redundancy to have two product tables. What you need is an extra field in your order table specifying the sold unit price of each product. And at the end of the day, when you want to count your money flow, you should refer to the unit price set in the order table.

For further development, you might need a history table for product prices. So that you can look back into history to analyze price adjustments.