I new in database design, I would like to be certain which i allow it to be well. Please have a look for a part of my database design:

My database design for fundamental shopping cart software:

//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

Hmm you will find two tables that you could remove out of your design: order_characteristics, product_characteristics. Otherwise, your choose query could be very slow needing to join from a lot of tables. You are able to keep characteristics as posts within the order and product table.

For my money, it's a poor design becasue it uses attribute tables whe are EAV tables and which could cause performance problems. Take time to really define the characteristics you need to have for that items, there actually are mostly simliar for many items (color, size, models(package of 10, single item, etc.). EAV may be the last measure.

Keep particulars from the prices, etc, within the orderdetail table. You don't want the cost to alter when the product cost changes afterwards in the future.

My structure could be somthing like: Order orderid, date, customerid Order particulars order_id,Compnay_id,Product_id, part_number,product_title, quantity, cost, Unit, Color, size, other characteristics Order notes order_id, note

Items product_id,Part_number, product_title, Company_id, product cost, color, size, Unit

Easier to have null posts (unless of course you've 100s of characteristics that you simply will not generally) when some items not have the same attrubutes) Further if you would like complete specs for any product, consider puttingthem inside a large varchar filed and placing a full text index onto it. This will perform much better than an EAV table.