silverkid silverkid - 3 months ago 8
MySQL Question

how to design this mysql database

there are 2 million users
each user has 4 tables . the data in 4 tables is not going to be appended and will remain fix.

the structure of each users tables will be same.

To store the data of these users in mysql i have to design a database.

do i need to create 2 million databases each with 4 tables ?

any help appreciated

the 4 tables are the the


  1. incoming call records of the user for 1 month

  2. incming sms records of the user for 1 month

  3. outgoing calls records of the user for 1 month

  4. outgoing sms records of the user for 1 month



the calls tables will have following structure

date time number duration charges

the incoming sms will have following structure

date time number

the outgoing sms will have following structure

date time number charges

Answer

This is an SQL antipattern that I call Metadata Tribbles. They look cute and friendly, but soon they multiply out of control.

As soon as you hear phrases beginning "I have an identical table per..." or "I have an identical column per..." then you probably have Metadata Tribbles.

You should start out by making one database with four tables, and add a user_id attribute to each of the four tables.

There are exception cases where you'd want to split into separate databases per user, but they are exceptions. Don't go there unless you know what you're doing and can prove that it would be necessary.