Scaling out MySQL & Redundancy-Speed tradeoff?

Question

I'm building an e-commerce service for a group of sellers. They have a common HQ who manufactures their product.

Tables:

1. order (id, seller_id, timestamp)
2. order_products (order_id, product_id, seller_id, timestamp, pincode)
3. transaction (id, seller_id, timestamp)
4. transaction_products (transaction_id, product_id, seller_id, timestamp, pincode)
5. seller (id, pincode, name)
6. product(id, price)

Specifications:

1. There are 100 sellers
2. Each vendor performs 500 transactions per day
3. Each transaction has 4 products associated with it
4. Each Vendor places two orders per day to HQ
5. Each order have 50 products

HQ Requirements:

1. How many products were sold by which seller in a given month
2. How many products were sold in a given pincode in a given month
3. Orders placed by all sellers in a given month

Seller Requirements:

1. View cost of order placed by him/her (the seller)
2. View his/her sales of a given month

The product is ready and application works just fine. But, I'm concerned with the two things.

1. Scaling: Being really new, I don't know much about scaling out or sharding or clustering. How much time have I got until I can keep these aside?
2. Redundancy: As you can see in transaction_product & order_product, I've reused columns from transaction & order, respectively. The redundant columns are: timestamp, seller_id, pincode. My idea was to avoid joins. But I'm not sure if joins would be more expensive than current redundancy. Can anyone point me in the current direction?

Answer 1

As you grow, keep an eye on the slowlog -- it will help you find the queries that need better indexes and/or reformulation.

No sharding for those small specs.

Clustering (eg, Galera or Innodb Cluster) give a high level of HA, plus some scaling.

Do not fear JOINs. On the other hand, don't "over-normalize".

If the monthly reports run to slowly, "Summary tables" can fix that.

No PARTITIONing unless you plan to purge "old" data.