SQL一对一关系与单表 [英] SQL one to one relationship vs. single table

问题描述

考虑下面的数据结构，用户具有少量的固定设置。

用户

  [Id] INT IDENTITY NOT NULL，
 [名称] NVARCHAR（MAX）NOT NULL，
 [电子邮件] VNARCHAR（2034）NOT NULL 
  

UserSettings

  [SettingA]，
 [SettingB]，
 [SettingC] 
  

将用户设置移动到单独的表格中被认为是正确的，从而创建一对一的关系与用户表？这是否比将其存储在与用户相同的行中提供任何实际的优势（明显的缺点是性能）。

解决方案

你当表变得非常宽（即有很多列）时，通常会将表分成两个或更多个1：1的相关表。程序员难以处理具有太多列的表。对于大公司，这样的桌子可以轻松拥有100列。

所以想象一个产品表。有一个售价，也许另一个价格只用于计算和估计。有两张桌子，一个是真正的价值观，一个在规划阶段是不是很好？所以程序员永远不会混淆这两个价格。或者为产品设置物流设置。您想要插入产品表中，但是其中包含所有这些逻辑属性，是否需要设置其中的一些？如果是两张表，您将插入到产品表中，另一位负责物流数据的程序员将关注物流表。没有更多的混乱。

另一个有许多列表的事情是，对于具有150列的表，对于只有一半的这个或更少。

最后一点是访问权限。使用单独的表，您可以对产品的主表和产品的后勤表授予不同的权限。

所以总之，看到1：1的关系是相当罕见的，但是他们可以更清楚地了解数据，甚至可以帮助性能问题和数据访问。

编辑：我正在采用Mike Sherrill的建议，（希望）澄清事情关于规范化。

正常化主要是为了避免冗余和相关缺乏一致性。仅在一个表或更多的1：1相关表中保存数据的决定与此无关。您可以决定在一张表中拆分用户表，以获取姓氏和姓氏等个人信息，并为他的学校，毕业和工作分配用户表。两个表将保持原始表格的正常形式，因为没有数据或多或少的冗余比以前。使用两次的唯一列将是用户ID，但这并不是多余的，因为在两个表中都需要标识记录。

所以问是否考虑正确的将设置规范化为单独的表格？不是一个有效的问题，因为您将数据放入1：1相关的单独表格中并不正常化。

Consider a data structure such as the below where the user has a small number of fixed settings.

User

[Id] INT IDENTITY NOT NULL,
[Name] NVARCHAR(MAX) NOT NULL,
[Email] VNARCHAR(2034) NOT NULL

UserSettings

[SettingA],
[SettingB],
[SettingC]

Is it considered correct to move the user's settings into a separate table, thereby creating a one-to-one relationship with the users table? Does this offer any real advantage over storing it in the same row as the user (the obvious disadvantage being performance).

解决方案

You would normally split tables into two or more 1:1 related tables when the table gets very wide (i.e. has many columns). It is hard for programmers to have to deal with tables with too many columns. For big companies such tables can easily have more than 100 columns.

So imagine a product table. There is a selling price and maybe another price which was used for calculation and estimation only. Wouldn't it be good to have two tables, one for the real values and one for the planning phase? So a programmer would never confuse the two prices. Or take logistic settings for the product. You want to insert into the products table, but with all these logistic attributes in it, do you need to set some of these? If it were two tables, you would insert into the product table, and another programmer responsible for logistics data would care about the logistic table. No more confusion.

Another thing with many-column tables is that a full table scan is of course slower for a table with 150 columns than for a table with just half of this or less.

A last point is access rights. With separate tables you can grant different rights on the product's main table and the product's logistic table.

So all in all, it is rather rare to see 1:1 relations, but they can give a clearer view on data and even help with performance issues and data access.

EDIT: I'm taking Mike Sherrill's advice and (hopefully) clarify the thing about normalization.

Normalization is mainly about avoiding redundancy and relateded lack of consistence. The decision whether to hold data in only one table or more 1:1 related tables has nothing to do with this. You can decide to split a user table in one table for personal information like first and last name and another for his school, graduation and job. Both tables would stay in the normal form as the original table, because there is no data more or less redundant than before. The only column used twice would be the user id, but this is not redundant, because it is needed in both tables to identify a record.

So asking "Is it considered correct to normalize the settings into a separate table?" is not a valid question, because you don't normalize anything by putting data into a 1:1 related separate table.

这篇关于SQL一对一关系与单表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！