Ігор Онек
Ігор Онек

Reputation: 270

Simple database scheme for shop

I have some problems with database architecture. I am doing a simple online shop by my own. I don`t know how to make logical database. I have User, Cart, CartItem, Pruduct, and Order tables.

My relations:

User OneToOne Cart
Cart OneToMany CartItem
CartItem ManyToOne Product

And i don`t know which relation should i choose for table Order. User can add any number of products to his cart, and after confirming purchase i want to store result into Order table. Please help me figure out it. I'm doing web store for first time and it is challenging for me.

With best regards. Cart

Entity
public class Cart {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
@OneToMany(mappedBy = "cart", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private List<CartItem> cartItems;
@OneToOne
private User user;
private Double grandTotal;

public Cart(List<CartItem> cartItems) {
    this.cartItems = cartItems;
}

public Cart(Double grandTotal) {
    this.grandTotal = grandTotal;
}

public Integer getId() {
    return id;
}

public void setId(Integer id) {
    this.id = id;
}

public List<CartItem> getCartItemsl() {
    return cartItems;
}

public void setCartItemsl(List<CartItem> cartItemsl) {
    this.cartItems = cartItemsl;
}

public User getUser() {
    return user;
}

public void setUser(User user) {
    this.user = user;
}

public Double getGrandTotal() {
    return grandTotal;
}

public void setGrandTotal(Double grandTotal) {
    this.grandTotal = grandTotal;
}
}

CartItem

@Entity
public class CartItem {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
@ManyToOne
@JsonIgnore
private Cart cart;
@ManyToOne
private Product product;
private Integer quantity;
private Double totalPrice;

public CartItem() {
}

public CartItem(Integer quantity, Double totalPrice) {
    this.quantity = quantity;
    this.totalPrice = totalPrice;
}

public Integer getId() {
    return id;
}

public void setId(Integer id) {
    this.id = id;
}

public Cart getCart() {
    return cart;
}

public void setCart(Cart cart) {
    this.cart = cart;
}

public Product getProduct() {
    return product;
}

public void setProduct(Product product) {
    this.product = product;
}

public Integer getQuantity() {
    return quantity;
}

public void setQuantity(Integer quantity) {
    this.quantity = quantity;
}

public Double getTotalPrice() {
    return totalPrice;
}

public void setTotalPrice(Double totalPrice) {
    this.totalPrice = totalPrice;
}

}

Product

@Entity
public class Product {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String name;
private String description;
private Float price;
@ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
private Category category;
@OneToMany(mappedBy = "product", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private List<CartItem> cartItemList;
private boolean available;

public Product() {
}

public Product(String name, String description, Float price, Category category, boolean available) {
    this.name = name;
    this.description = description;
    this.price = price;
    this.category = category;
    this.available = available;
}

public Integer getId() {
    return id;
}

public void setId(Integer id) {
    this.id = id;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public String getDescription() {
    return description;
}

public void setDescription(String description) {
    this.description = description;
}

public Float getPrice() {
    return price;
}

public void setPrice(Float price) {
    this.price = price;
}

public Category getCategory() {
    return category;
}

public void setCategory(Category category) {
    this.category = category;
}

public List<CartItem> getCartItemList() {
    return cartItemList;
}

public void setCartItemList(List<CartItem> cartItemList) {
    this.cartItemList = cartItemList;
}

public boolean isAvailable() {
    return available;
}

public void setAvailable(boolean available) {
    this.available = available;
}

}

Upvotes: 1

Views: 151

Answers (1)

cartalot
cartalot

Reputation: 3158

When the User completes the purchase you write it to the Order table and you write the user's cart to Order Items. The order should have the Billing address and Shipping address in the order record.

Optionally you can also write the users billing information to a Customer table and a Shipping table. (One Customer can have many Shipping addresses) It might be tempting to then not include that info in the order record and link to those records - do not do this. There are a bunch of complications that can arise but in short the information for the specific order has to remain part of the order.

This way all of the tables that you need to keep for historical purposes of the orders - are completely separate from the tables used for shopping.

So it sounds like your 'cart' is really the beginnings of the 'order' with running totals. Thats cool but you might want to name it something else.

The important thing is that the cart items should be periodically checking the Products table to confirm the price, and confirm the product is still in stock. Either of these things changing can create huge issues for the store especially if a customer orders something that has gone out of stock. The store can lose all its profit in the customer service time that it takes to contact the customer and thats IF they can convince the customer to purchase a different item. And obviously if the product price has gone up then the store loses that money on the sale.

So that means the only responsibility the Cart (cart items) has is to hold the product sku (item number) and the Quantity. The cart can hold the price for display but its not responsible for the price. At a minimum the cart should check with the products table for price and stock levels after the shipping information is given and before the final click to generate the transaction.

Its very good that you have the idea of a User it will make things much easier. So one thing to think about is having the User hold the different running totals - versus the 'cart' that it looks like you are doing. Because there are going to be things that affect the in process order running totals - like the shipping, sales, tax, etc - that have nothing to do with the 'cart'.

Another idea is for example saving the in process order shipping information to the User. In other words they have submitted shipping info but the transaction has not gone through yet. That way if they never complete the purchase - which happens a great deal - you haven't wasted any resources writing to a shipping table. And if you only write to the shipping table for completed orders, then every record is valid. Same idea for billing.

Upvotes: 2

Related Questions