与不同数据类型的多对多关系 [英] Many-to-many relationship with different data types

问题描述

我们的应用程序已经被定制以处理许多不同类型的客户，具有仅适用于几个或一个客户的某些设置。
我决定添加一个[Settings]表，以允许每个设置为一行。而不是不断地向客户表中添加可空的列，

  [dbo]。[设置] 
 [SettingID] [int] 
 [SettingCode] [nchar]（4）
 [SettingDescription] [nvarchar]（255） 
  

然后通过多对多表将其链接到[Customers]表

  [dbo]。[Customer_Settings] 
 [Customer_SettingsID] [int] 
 [CustomerID] [int] 
 [SettingID] [int] 
  

我的问题是关于如何处理这些事实，

例如，我们可以有一个设置为最新交货时间，需要时间数据类型，或另一个设置为分钟直到到期需要一个int。

我可以想到的两种方法来处理这个问题是向[Customer_Settings]表中添加可空列，如：

  [dbo]。[Customer_Settings] 
 [Customer_SettingsID] [int] 
 [CustomerID] [int] 
 [SettingID] [int] 
 [ValueTime] [time] NULL 
 [ValueInt] [int] NULL 
 ... 
  pre> 
 
 
这似乎是糟糕的设计。
 
 
 
另一种方式我可以想到的是将子表添加到[Customer_Settings]表如：
  [dbo]。[Customer_Settings_Int] 
 [Customer_Settings_Int_ID] [int] 
 [Customer_SettingsID] [int] 
 [Value] [int] 
  
这似乎是正常化，但也很繁琐。请让我知道，如果其中一个明显更好，或者有另一个选择。谢谢！
解决方案
您选择的解决方案称为实体属性值（EAV。）最常见的方法是将所有值存储为字符串。有些人添加一个验证器列，其中包含正则表达式或类似表达式，由客户端验证或t-sql函数更新该值。
 
 
 
它更清洁使用可空列。
 
Our app has been customized to handle many different types of customers, with certain settings that will only apply to a few or one customer.  Rather than continuously adding nullable columns to the customers table, 
I decided to add a [Settings] table to allow each setting to be a row.
[dbo].[Settings]
    [SettingID] [int] 
    [SettingCode] [nchar](4)
    [SettingDescription] [nvarchar](255) 
Which is then linked to the [Customers] table through a many-to-many table
[dbo].[Customer_Settings]
    [Customer_SettingsID] [int]
    [CustomerID] [int] 
    [SettingID] [int] 
My question is about how to handle the fact that many of these settings need an additional data type on the [Customer_Settings] table.

For example, we could have one setting be "Latest Delivery Time"  requiring a time datatype, or another be "Minutes Until Expiration" requiring an int.

The two ways I can think of to handle this is to add nullable columns to the [Customer_Settings] table like:
[dbo].[Customer_Settings]
    [Customer_SettingsID] [int]
    [CustomerID] [int] 
    [SettingID] [int] 
    [ValueTime] [time] NULL
    [ValueInt] [int] NULL
    ...
This seems like bad design.

The other way I can think of is to add child tables to [Customer_Settings] table like:
[dbo].[Customer_Settings_Int]
    [Customer_Settings_Int_ID] [int]
    [Customer_SettingsID] [int]
    [Value] [int]
This seems like it is normalized but also cumbersome.  Please let me know if one of these is clearly better or if there is another alternative.  Thanks!
解决方案
The solution you have chosen is called Entity-Attribute-Value (EAV.) The most common approach is to store all values as strings.  Some people add a validator column, containing a regex or like expression, that is verified by the client or t-sql function updating the value.

It is much much cleaner to use nullable columns.

                        
这篇关于与不同数据类型的多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！