you will find two million customers each user has 4 tables . the information in 4 tables won't be appended and can remain fix.

the dwelling of every customers tables is going to be same.

To keep the information of those customers in mysql i must design a database.

should i create two million databases each with 4 tables ?

any help appreciated

some tables would be the the

  1. incoming call records from the user for 30 days
  2. incming sms records from the user for 30 days
  3. outgoing calls records from the user for 30 days
  4. outgoing sms records from the user for 30 days

the calls tables may have following structure

date time number duration charges

the incoming sms may have following structure

date time number

the outgoing sms may have following structure

date time number charges

It is really an SQL antipattern which i call Metadata Tribbles. They appear cute and friendly, but soon they multiply unmanageable.

The moment you hear phrases beginning "I've the same table per..." or "I've the same column per..." then you definitely most likely have Metadata Tribbles.

You can start out by looking into making one database with four tables, and give a user_id attribute to each one of the four tables.

You will find exception cases when you would split up into separate databases per user, but they're exceptions. Don't visit unless of course guess what happens you are doing and may prove that it might be necessary.

Most database servers (and filesystems) won't handle instances needing that lots of separate databases. I am speculating that that which you really require are four tables, each that contains a row for every user. This is a totally reasonable requirement.

No, you don't need two million databases with 4 tables each. You simply need 1 database, 4 tables, and different user ID for every user.

Something similar to:

users table:
| user_id (primary key) | username |

address table
| user_id (foreign key) | address  |

whatever table
| user_id (foreign key) | whatever |

I must question the reason why you need 4 tables per user? Hopefully you realize the fundamentals of the items I am attempting to convey here though.

Think about this example -- lent from data warehousing -- it's a plain Kimball star. Very simplified model, however these four tables cover your referred to needs, and much more.

alt text

You simply need a table for customers, table1, table2, table3, table4. then when you really need to go in data to table1 just range from the customers_id to table1

TABLE 1                              USERS

ID   usersID     amount   quantity   ID   Name
1       1           200      2        1   John
2       1           400      3

You are able to connect then using INNER JOIN by its table1.usersID=customers.ID All table should have usersID(expect customers table that has ID) for connecting them

We do hope you understand

No, you don't need two million databases!

You really need two million instances for 4 tables.

Obviously, you need to design a relational database so that each table includes a relational attribute for other tables.