I am using MySQL with innodb.
My app is used by business owners (users), with primary key: user_phone. The app will have a maximum of 100K users. Each user will have around 400 customers, with primary key: customer_phone, and 10 other fields. Each user also has around 30K messages (3 fields: subject, message, sent_datetime) sent to those 400 customers.
So, I have 2 options:
Option 1: To make 2 tables for all customers, and messages combined in these 2 tables:
Table Customer:
user_phone customer_phone field1 field2 field3 .... field10
Primary key => user_phone, customer_phone
Total rows: 100k (users) X 400 (customers)
And
Messages:
user_phone customer_phone subject message sent_datetime
Primary key => user_phone, customer_phone, subject, message, sent_datetime
Total rows: 100k (users) X 30K (messages)
As you can see that the tables are only two, but it has many million rows.
Or
Option 2: I can have individual table for each user, the table name has user_phone number in it:
Table customers_<user_phone> eg. customers_888111222:
customer_phone field1 field2 field3 .... field10
Primary key => customer_phone
Total rows in each table: 400 (customers)
and similarly for messages: Table name: messages_<user_phone> eg. messages_888111222: Total rows in each table: 30K (messages).
Total tables (2 tables for each user): 500k (for customers) + 500k (for messages) = 1 million tables but with much less rows.
So, either I can have 2 tables with 40 million of rows, or 1 million tables with some hundreds rows in each.
I have one more option, kind of a middle path: to have 50 tables: one for each state. And each state's table will have customer data of those users who belong to that state. So, there will be total 50 (customers) + 50 (messages) = 100 tables with around a million rows each.
Please suggest the correct database structure.
And what if the users increase to like 500k from initial 100k. that will increase rows to 5 times if only 2 tables are used.
