MySQL：多个表或多个列的表？ [英] MySQL: multiple tables or one table with many columns?

问题描述

所以这更像是一个设计问题。我有一个主键说用户的ID，我有大量的信息与该用户相关联。我应该根据信息将多个表分成几个类别，或者我应该只有一个表有很多列？

So this is more of a design question. I have one primary key say the user's ID, and I have tons of information associated with that user. I am concerning should I have multiple tables broken down into categories according to the information or should I have just one table with many many columns?

我以前做的方式要有多个表，所以说一个表用于应用程序使用数据，一个表用于配置文件信息，一个表用于后端令牌等，以保持内容的组织。最近有人告诉我，最好不要这样做，并且有一张表有很多列是好的。所有这些列都有相同的主键。

The way I used to do it was to have multiple tables, so say one table for application usage data, one table for profile info, one table for back end tokens and etc, to keep things look organized. Recently some one told me that it's better not to do it and having a table with lots of column is fine. The thing is all those columns have the same primary key.

我对数据库设计相当新鲜，哪种方法更好，有什么优点？传统的方式是什么？

I'm pretty new to database design so which approach is better and what are the pros and cons? What's the conventional way of doing it?

推荐答案

任何时间信息是一对一的（每个用户都有一个名字和密码），那么它可能更好的是拥有一个表，因为它减少了数据库需要做的检索结果的连接数。我认为一些数据库对每个表的列数有限制，但是在正常情况下我不用担心，如果需要，您可以随时拆分。

Any time information is one-to-one (each user has one name and password), then it's probably better to have it one table, since it reduces the number of joins the database will need to do to retrieve results. I think some databases have a limit on the number of columns per table, but I wouldn't worry about it in normal cases, and you can always split it later if you need to.

如果数据是一对多（每个用户有数千行的使用信息），那么它应该被拆分成单独的表，以减少重复数据（重复数据浪费存储空间，缓存空间，并使数据库更难维护）。

If the data is one-to-many (each user has thousands of rows of usage info), then it should be split into separate tables to reduce duplicate data (duplicate data wastes storage space, cache space, and makes the database harder to maintain).

您可能会在数据库规范化中找到维基百科文章有趣，因为它深入讨论了这个原因：

You might find the Wikipedia article on database normalization interesting, since it discusses the reasons for this in depth:

数据库规范化是组织这些领域和表格的过程关系数据库，以尽量减少冗余和依赖。标准化通常包括将大型表分成更小（更少冗余）的表，并定义它们之间的关系。目的是隔离数据，以便在一个表中添加，删除和修改字段，然后通过定义的关系传播到数据库的其余部分。

Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

非规范化也是需要注意的，因为有些情况下重复数据更好（因为它减少数据库在读取数据时需要做的工作量）。我强烈建议您尽可能规范化数据，并且只有在特定查询中意识到性能问题时，才能进行非规范化。

Denormalization is also something to be aware of, because there are cases where repeating data is better (since it reduces the amount of work the database needs to do when reading data). I'd highly recommend making your data as normalized as possible to start out, and only denormalize if you're aware of performance problems in specific queries.

这篇关于MySQL：多个表或多个列的表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！