(database design,mysql) Is my database design good for basic shopping cart?(I new in database design)

Question

Hi, I new in database design, I want to be sure that i make it well. Please take a look for part of my database design:

My database design for basic shopping cart:

//table that holds shopping cart items that customer choose(not press checkout and order //them)

**shopping_cart**
{
id (int)
product_id (int) fk
product_quantity (int)
customer_user_id (int) fk 
}

//table that holds product order data in time of checkout.(i hold them because supplier //can change after time products attributes value add some attributes or delete and change //the price of product)

**order**
{
id (int)
product_id (int)  fk
customer_user_id (int)  fk
}


//table that connect order  to attribute table for products attributes value in the moment //of   checkout

**order_attributes**
{
id (int)
order_id (int)  fk
attribute_id (int)  fk
}

//main product table
**product**
{
id (int)
sku (int) 
product_name (varchar)
supplier_user_id (int)  fk
}

//connection table many to many 
**product_attributes**
{
id (int) 
product_id (int)  fk
attribute_id (int)  fk
}

//table that holds products attributes (price, weight, color + new attributes that user //will create)

**attribute**
{
id (int)
product_id (int)  fk
attribute_name (varchar)
attribute_value(varchar)
}

Thanks you

Answer 1

For my money, it is a poor design becasue it uses attribute tables whe are EAV tables and which can cause performance problems. Take the time to actually define the attributes you want to have for the products, there really are mostly simliar for most products (color, size, units(package of 10, single item, etc.). EAV is the last resort.

Store the details of the prices, etc, in the orderdetail table. You do not want the price to change if the product price changes later on down the road.

My structure would be somthing like: Order orderid, date, customerid Order details order_id,Compnay_id,Product_id, part_number,product_name, quantity, price, Unit, Color, size, other attributes Order notes order_id, note

Products product_id,Part_number, product_name, Company_id, product price, color, size, Unit

Better to have null columns (unless you have hundreds of attributes which you won't generally) when some products don't have the same attrubutes) Further if you want complete specs for a product, consider puttingthem in a large varchar filed and putting a full text index on it. This should perform way better than an EAV table.

Answer 2

Hmm there are two tables that you can remove from your design: order_attributes, product_attributes. Otherwise, your select query would be very slow having to join from so many tables. You can store the attributes as columns in the order and product table.