什么时候将模型分成多个数据库表? [英] 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.

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

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?

要为此增加更多的光泽，除了特定于它们的页面之外，很少访问大多数字段.例如，只有当某人单击进入用户的个人资料时，才能访问生日之类的信息.此外，某些字段(很少访问)可能具有极大的潜力.大多数字段都可能设置为空白或为零.

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.

推荐答案

通常，将具有一对一关系的事物放在同一张表中通常是一个好主意.除非您的用户群包括皇后熊"或帕丁顿熊"，否则用户只有一个生日，因此应将其作为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屋！