-2

I am currently working on a php website project which contains monthly data of 10,000 customers.

The customer has to login to view his/her personal historical monthly purchases and sales data. The system admin has to update the monthly purchases and sales of each customers at the end of each month.

When logged in the customer can view his/her historical monthly purchases and sales data for the last 120 months. The historical data of each customer is stored in mysql database. Here are my questions:

(1) Can anyone help me how to structure my database tables so that each customer can view his/her historical data after login?

(2) Will there be limitation of the number of rows and columns of a table when I deal with 10,000 customers with historical monthly data for many years?

ADyson
  • 57,178
  • 14
  • 51
  • 63
Mamu
  • 1
  • https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html#column-count-limits – ADyson Nov 16 '21 at 17:02
  • 10,000 is a comparatively tiny number of customers. Even in the unlikely scenario that all of them had purchased 1000 items, you'd still only have 10,000,000 rows in the sales history table, which while starting to get big, should still not be especially problematic given proper indexing etc. Beware of premature optimisation. – ADyson Nov 16 '21 at 17:03
  • a separate table for `sales` will be good. with columns including a customer id and date. the customer id will be the user id which will be stored in the users table. – Albert Logic Einstein Nov 16 '21 at 17:05
  • Anyway this question needs to be more focused. Where exactly are you stuck? Have you attempted to make a design and encountered a problem? – ADyson Nov 16 '21 at 17:08
  • Dear @ADyson I know how to code the login script. The problem I have is how to structure the tables. I was thinking of having one table for sales and another for purchase. The table column includes customer_id, date, and sales_value (for sales table) and purchase_value for purchase table. My concern is the total rows will be in millions and I am not sure if it will exceed the mysql row limits. – Mamu Nov 16 '21 at 17:19
  • There is no limit on rows which would realistically affect you. Have you done any research about that? It's not hard to find information from a simple Google search e.g. https://stackoverflow.com/questions/2716232/maximum-number-of-records-in-a-mysql-database-table – ADyson Nov 16 '21 at 17:41
  • It's up to you but I doubt you should have two tables for sales and purchase because they'd be extremely similar. Probably a more normalized structure would be to have a "transaction" table, where the type of transaction (e.g. sale or purchase) is stored in one of the columns. Study database normalisation in detail to firm up your understanding of the issues. Maybe consult a professional DBA about wider issues. And study how other commerce / finance systems store this kind of data. – ADyson Nov 16 '21 at 17:43
  • What is the estimated total number of purchases by all customers over the 10 years? – Rick James Nov 16 '21 at 23:18

1 Answers1

1

You are nowhere near the limits. "Millions" of rows -- that is medium-sized. "Billions" is when it gets exciting. On the other hand, performance may be an issue.

  • Will you ever delete "old" data? If so, we need to talk about Partitioning.

  • It is probably best to have the PRIMARY KEY of the main table start with customer_id, date. This will address many performance issues.

  • As for what tables to have, think of what "entities" you have: customer and transaction, but not much else. (I don't understand why "sales" and "purchases" are different.) As you develop the queries, you will discover other issues, such as an extra table to handle customers with multiple phones (landline, cell, fax, home, work, etc).

  • "at the end of each month" -- why not store each transaction as it happens?

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • 1
    This answer seems to have more questions in it than answers. Rhetorically speaking, should this question be answered at all? – mickmackusa Nov 17 '21 at 03:38
  • @mickmackusa - I find that a user's question is often "I backed myself into this particular corner, how do I fix the mess I made?" Sometimes the answer is to approach the task in a different way. My plethora of questions may get them to abandon their plan, or it may help focus the question is some direction that they did not [yet] think of. – Rick James Nov 17 '21 at 16:59