why make a new "CartItem" entity for the cart if the number of entries in the database does not change solely for readability?

Question

Without going into details, right now I have two entities: Invoice and Product. Is it worth creating a new CatItem entity with literally one property "Quantity" (and relationship properties between tables), if the total number of records in the table is not reduced, but a new table is added (if this judgment is wrong, correct me)

Invoice code:

    public class Invoice
    {
        [Key]
        public long Id { get; set; }

        ///Any property

        public virtual ICollection<Product> CurrentProducts { get; set; } = new HashSet<Product>();
        
        ///Any property
 
    }

Product code: anything can be a product

    public class Product
    {
        [Key]
        public int Id { get; set; }

        ///Any property

        public virtual ICollection<Invoice> Invoices { get; set; } = new HashSet<Invoice>();

        ///Any property
    }

CatItem code:

    public class CatItem
    {
        [Key]
        public int Id { get; set; }

        ///Any property

        public short Quantity { get; set; }

        public virtual int ProductId { get; set; }     
        public virtual Product Product { get; set; }

        public virtual long InvoiceId { get; set; }     
        public virtual Invoice Invoice { get; set; }

        ///Any property
    }

database with/Without CartItem example: enter image description here

I understand what to do through cartItem is more correct, but isn't there a better practice for implementing a cart?

Answer 1

If the Product table is supposed to be used only as a list of items on the invoice, then it probably will be enough, though you should at least rename the Product table to something like InvoicePosition.

If you intend to use the Product table somewhere else, like to list available products in your store, then you definitely want to normalize the tables. The Product table from your many-to-many example would not work at all in such (and many others) scenario. In your many-to-many example, you have multiple products with the same names but different prices. And for each invoice, you will need a separate position there. I can't imagine using it for anything else than just as an InvoicePosition table.

If you want to further normalize your tables, and make them more flexible, you may want to consider creating a separate Price table, with ProductId, current price, and dates range for when the such price is available. As it looks like based on the 'apples' product, you want to support changing prices for a given product.

So, a little bit more flexible db scheme could look like that:

public class Invoice {
   public long Id { get; set; }
   public long CartId { get;set; }
   public List<InvoicePosition> Positions { get; set; }
}

public class InvoicePosition {
  public long Id { get; set; }
  public long InvoiceId { get; set; }
  public int CartItemId { get; set; }
  public decimal Value { get; set; }
  public int Quantity { get; set; }
}

public class Product {
  public int Id { get; set; }
  public string Name { get; set; }
  public AvailableQuantity { get; set; } // should be probably also moved to some kind of a warehouse model
  public List<Price> Prices { get; set; }
}

public class Price {
  public long Id { get; set; }
  public int ProductId { get; set; }
  public decimal Value { get; set; }
  public DateTimeOffset From { get; set; }
  public DateTimeOffset To { get; set; }
}

public class Cart {
   public long Id { get; set; }
   public List<CartItem> Items { get;set; }
}

public class CartItem {
   public long Id { get; set; }
   public long CartId { get; set; }
   public int ProductId { get; set; }
   public long PriceId { get; set; }
   public int Quantity { get; set; }
}

I hope, most of this is fairly simple to understand. You may have a question, what is the benefit of referencing CartItem inside the InvoicePosition instead of simply referencing a Product? It's not entirely necessary, however, it would help you to apply and easier track additional price modifiers like discounts or special promotions. And having the history of what has been ordered and what has been invoiced (they definitely may differ, for example when there was a missing item in the warehouse after the order has been made) can definitely be very useful.