何时将模型拆分为多个数据库表? [英] When to split up models into multiple database tables?

问题描述

我正在使用 Ruby on Rails，但我认为这个问题比这更广泛，并且通常适用于数据库设计.

I'm working with Ruby on Rails, but this question I think is broader than that and applies to database design generally.

什么时候将单个模型拆分为多个表是个好主意?例如，假设我有一个 User 模型，并且模型中的字段数量确实开始增加.例如，用户可以输入他的网站，他的生日，他的时区，他的等等.

When is it a good idea to split a single model up into multiple tables? For example, assume I have a User model, and the number of fields in the model is really starting to add up. For example, the User can enter his website, his birthday, his time zone, his etc etc.

拆分模型是否有任何优点或缺点，例如可能 User 表只有基本信息，例如登录和电子邮件，然后每个用户都有另一个表，类似于 UserInfo，另一个表是UserPermissions，另一个是 UserPrivacySettings 或类似的东西?

Is there any advantage or disadvantage to splitting up the model, such that maybe the User table only has basic info like login and email, and then there is another table that every User has that is something like UserInfo, and another that is UserPermissions, and another that is UserPrivacySettings or something like that?

为了对此添加额外的说明，大多数字段很少被访问，除了特定于它们的页面.例如，只有当有人点击进入用户的个人资料时，才能访问诸如生日之类的内容.此外，一些字段(很少访问)有可能非常大.大多数字段都有可能设置为空白或 nil.

To add additional gloss on this, most of the fields are rarely accessed, except on pages specific to them. For example, things like birthday are only ever accessed if someone clicks through to a User's profile. Furthermore, some of the fields (which are rarely accessed) have the potential to be extremely large. Most of the fields have the potential to be either set to blank or nil.

推荐答案

通常将具有一对一关系的事物放在同一个表中是个好主意.除非您的用户群包括 Queen 或 Paddington Bear，否则用户只有一个生日，因此这应该是 USERS 表的一个属性.具有一对多关系的事物应该在单独的表中.所以，如果一个用户可以有多个隐私设置，一定要把它们分开.

Generally it is a good idea to put things which have a one-to-one relationship in the same table. Unless your userbase includes the Queen or Paddington Bear, a user has just one birthday, so that should be an attribute of the USERS table. Things which have a one-to-many relationship should be in separate tables. So, if a user can have multiple privacy settings by all means split them out.

如果我们想一次检索所有用户的信息，将一张表拆分为几张表会使查询变得更复杂或更慢.另一方面，如果我们有一组仅以离散方式查询或更新的属性，那么拥有一个单独的表来保存该数据是一个不错的主意.

Splitting one table into several tables can make queries more complicated or slower, if we want to retrieve all the user's information at once. On the other hand if we have a set of attributes which is only ever queried or updated in a discrete fashion then having a separate table to hold that data is a sound idea.

这篇关于何时将模型拆分为多个数据库表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！