Appropriate Database Schema for ecommerce

Question

lets say we have a ecommerce website for ordering items that must be customized.

The schema I have in mind is:

• A table for Order, each row being an order
• A table for the Order_Item, each row being an item of an order ( foreign key order_id)
• A table for the Order_Item_Option, each row being an option of an item of an order ( foreign key order_item_id)

The website also has the possiblity to save customer cart in the database, before the order. My question is: before the order, how would you store the cart ?

A- Would you put the data in the same database ( orders, orders_items, order_items_option) with the same logic, with a specific status, and simply update the status at order.

B- Duplicate the same schema, but exclusively for cart only.

I'd like to go with option A, but I am afraid that fetching the cart will eventually get too slow, after few weeks/months. I expect the table with order_item_option will grow quickly, probably around 50/100K row per month. So how would you do it ?

Answer 1

It would be best to go with Option B, because cart items (sometimes called queue) can grow over time, but with no significant use for you. If you have a separate setup for carts, you can handle the archival of old records better, without keeping to much load on the orders database, as the orders database may also be queries for listing of past orders as well.

(Reposting my earlier comment as an answer, just in case it has the scope of being marked as an accepted answer. )