3

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

Improve this question
4
  • What "data" exactly is in the 4 tables? Please elaborate. Commented Nov 18, 2009 at 5:43
  • 1
    Oh, good lord no. Why do you think that each user will need to have four tables? We need more details on your requirements. Commented Nov 18, 2009 at 5:44
  • wtf, do you have any more details? Commented Nov 18, 2009 at 5:45
  • please check out the edits in the question everybody Commented Nov 18, 2009 at 5:52

6 Answers 6

Reset to default
10

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I love the name "Metadata Tribbles". That by itself is worth an upvote.
4

Most database servers (and filesystems) will not handle instances requiring that many separate databases. I'm guessing that what you actually require are four tables, each containing a row for each user. That's a totally reasonable requirement.

Improve this answer

Comments

3

No, you do not need 2 million databases with 4 tables each. You just need 1 database, 4 tables, and unique user ID for each user.

Something like:

users table: | user_id (primary key) | username | address table | user_id (foreign key) | address | whatever table | user_id (foreign key) | whatever |

I have to wonder why you need 4 tables per user? Hopefully you understand the basics of what I'm trying to convey here though.

Improve this answer

Comments

1

Consider this example -- borrowed from data warehousing -- it is a plain Kimball star. Very simplified model, but these four tables cover your described needs, and more.

telecom_model_01

Improve this answer

Comments

0

You just need a table for users, table1, table2, table3, table4. then when you need to enter data to table1 just include the users_id to table1

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

You can connect then using INNER JOIN by its table1.usersID=users.ID All table must have usersID(expect users table which has ID) to connect them

Hope you get the point

Improve this answer

Comments

0

No, you do not need 2 million databases!

You actually need 2 million instances for 4 tables.

Of course, you should design a relational database such that each table has a relational attribute for other tables.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.