0

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?
  • 1
    100 vendor × 500 trx × 4items = 200 000 rows/day. My rasberry pi/cell phone can handle this – Michael Kutz Jul 9 '20 at 18:52
  • The only way to answer scalability questions is to test it on your hardware with your workload. – mustaccio Jul 9 '20 at 20:10
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.