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.
有200万用户,每个用户有4个表。 4个表中的数据不会被追加,并将继续修复。
the structure of each users tables will be same.
每个用户表的结构都是一样的。
To store the data of these users in mysql i have to design a database.
要将这些用户的数据存储在mysql中,我必须设计一个数据库。
do i need to create 2 million databases each with 4 tables ?
我需要创建200万个数据库,每个数据库有4个表吗?
any help appreciated
任何帮助赞赏
the 4 tables are the the
4个表是
- incoming call records of the user for 1 month
- incming sms records of the user for 1 month
- outgoing calls records of the user for 1 month
- outgoing sms records of the user for 1 month
用户来电记录1个月
用户短信记录1个月
用户的拨出电话记录为1个月
用户的传出短信记录1个月
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
日期时间编号收费
6 个解决方案
#1
9
This is an SQL antipattern that I call Metadata Tribbles. They look cute and friendly, but soon they multiply out of control.
这是一个SQL反模式,我称之为Metadata Tribbles。他们看起来很可爱和友好,但很快就会失控。
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.
一旦你听到“每个......我有一个相同的表”或“每个......我有一个相同的列”的短语,那么你可能有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.
您应该首先创建一个包含四个表的数据库,然后将user_id属性添加到四个表中的每个表中。
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.
在某些例外情况下,您希望将每个用户拆分为单独的数据库,但它们是例外情况。除非你知道自己在做什么,否则不要去那里,并且可以证明这是必要的。
#2
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.
大多数数据库服务器(和文件系统)都不会处理需要许多单独数据库的实例。我猜你实际需要的是四个表,每个表包含每个用户的一行。这是一个完全合理的要求。
#3
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.
不,您不需要200万个数据库,每个数据库有4个表。您只需要为每个用户提供1个数据库,4个表和唯一的用户ID。
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.
我不知道为什么每个用户需要4个表?希望您了解我在此尝试传达的基础知识。
#4
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.
考虑这个例子 - 借用数据仓库 - 它是一个普通的Kimball明星。非常简化的模型,但这四个表格涵盖了您描述的需求等等。
#5
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
你只需要一个用户表table1,table2,table3,table4。然后,当您需要向table1输入数据时,只需将users_id包含到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
您可以使用INNER JOIN通过其table1.usersID = users.ID连接所有表必须具有usersID(期望具有ID的users表)才能连接它们
Hope you get the point
希望你明白这一点
#6
0
No, you do not need 2 million databases!
不,你不需要200万个数据库!
You actually need 2 million instances for 4 tables.
实际上,4个表需要200万个实例。
Of course, you should design a relational database such that each table has a relational attribute for other tables.
当然,您应该设计一个关系数据库,使每个表都具有其他表的关系属性。
#1
9
This is an SQL antipattern that I call Metadata Tribbles. They look cute and friendly, but soon they multiply out of control.
这是一个SQL反模式,我称之为Metadata Tribbles。他们看起来很可爱和友好,但很快就会失控。
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.
一旦你听到“每个......我有一个相同的表”或“每个......我有一个相同的列”的短语,那么你可能有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.
您应该首先创建一个包含四个表的数据库,然后将user_id属性添加到四个表中的每个表中。
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.
在某些例外情况下,您希望将每个用户拆分为单独的数据库,但它们是例外情况。除非你知道自己在做什么,否则不要去那里,并且可以证明这是必要的。
#2
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.
大多数数据库服务器(和文件系统)都不会处理需要许多单独数据库的实例。我猜你实际需要的是四个表,每个表包含每个用户的一行。这是一个完全合理的要求。
#3
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.
不,您不需要200万个数据库,每个数据库有4个表。您只需要为每个用户提供1个数据库,4个表和唯一的用户ID。
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.
我不知道为什么每个用户需要4个表?希望您了解我在此尝试传达的基础知识。
#4
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.
考虑这个例子 - 借用数据仓库 - 它是一个普通的Kimball明星。非常简化的模型,但这四个表格涵盖了您描述的需求等等。
#5
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
你只需要一个用户表table1,table2,table3,table4。然后,当您需要向table1输入数据时,只需将users_id包含到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
您可以使用INNER JOIN通过其table1.usersID = users.ID连接所有表必须具有usersID(期望具有ID的users表)才能连接它们
Hope you get the point
希望你明白这一点
#6
0
No, you do not need 2 million databases!
不,你不需要200万个数据库!
You actually need 2 million instances for 4 tables.
实际上,4个表需要200万个实例。
Of course, you should design a relational database such that each table has a relational attribute for other tables.
当然,您应该设计一个关系数据库,使每个表都具有其他表的关系属性。