mysql - 不定数量的表与不定数量的多列行

Question

动态创建表或动态添加行的数据库哪个更好(性能和维护方面)？

假设我正在构建一个让用户注册的项目。假设我有一张表只存储基本的个人信息，例如姓名、出生日期、加入日期、地址、电话等。假设有 10 列。

现在是棘手的部分。

场景一:创建多个表

当用户完成注册时，将创建一个消息表。因此，每个表都是为每个用户创建的。每个消息表的行因用户而异。

以同样的方式，每个用户都有一个购物车表，就像消息表一样。

对于这个场景 1，每次注册都会创建 2 个表。

场景 2:添加行

此处的场景也相同，但在本例中我有 2 个表用于消息和购物车。仅当有事件时才添加行。

注意:

您必须假设用户数量超过 2000 人，并期望有 50 多个用户始终处于事件状态。这意味着消息和购物车表在这两种情况下总是很忙。就像总是同时查询更新、添加、删除、插入、选择等。

还有哪个场景会占用更多的磁盘空间。

写这篇文章时，我想知道 Facebook 和其他人会使用什么技术。如果他们使用场景 2 样式(所有用户(十亿)使用相同的大长消息表)......只是想知道

Answer 1

Databases has some basic rules defined for Database Design called "Database Normalization", These basic rules allow us eliminating redundant data.

1st Normal Form
Store One piece of information in only One Column, A column should store only One piece of information.

2ns Normal Form
A Table should have only the columns that are related to each other. All the related columns should be in One table.

Now if you look at your advised design, A Separate Table for each USER will split SAME information/Columns about all the user in 1000's of tables. Which violates the 2nd Normal Form.

You need to Create One Table and put all the related Columns in that one table for all the users. and you can make use of normal t-sql to query your data but if you have a table for each user my guess is your every query that you execute from your application will be built dynamically and for every query you will be using dynamic sql. which is one of the Sql Devils and you want to avoid using it whenever possible.

My suggestion would be read more about Database Design. Once you have some basic understanding of database design. Draw it on a piece of paper and see if it provides you everything that your business requires / expects from this application , Spend sometime on it now it will save you a lot of pain later.