在单个Sql Server表中存储200多个属性值的最佳方法是什么？ [英] What is best way to store more than 200 attributes value in single Sql Server table?

问题描述

在单个数据库表中使用超过200列的好方法吗？虽然在一个记录中只有20列具有该值，而其余180将具有空值？

示例 - 假设我有20个对象，每个对象具有15个属性（属性），其中5个属性对于所有10个属性是共同的对于所有20个对象，对象和其他10个属性是不同的。

这里我为Common属性定义了5个列，180个属性的其余部分是什么？任何人都有好主意以最佳方式存储这些数据。

解决方案

回答一个的替代方法（这是一个好方法）是依赖于两个表的答案标签。



表1包含您的主要密钥数据。



表2使用ref来表示主要数据键，标签和值。



标签等同于列名。您可以根据需要为每个引用添加任意数量的标记值。在实际使用之前无需创建标记类型。



这种标记（而不是列或标准化）的优点是它可以扩展而不添加任何额外的你的参考价值。值只是因为你将字符串用作标记而成为词汇表的一部分。







澄清/范例



表1

 recid Your_Main_Data 
  1 小猫
  2 蟑螂
 
 表  2 （替换所有  列）
 ref tag value 
  1  pet domestic 
  1 品尝
  2  bug讨厌
  2 味道苦涩
  2 宠物vermon 
 
所以
 列 '  ref' tie  in   您的主表格 
 列 '  tag'将 like  a 列标头
 列 '   value'将是列下的内容
 
注意只使用你需要的那些。 
由于' 不是标签的外部列表，因此您可以随时使用任何内容。 

首先，这太模糊了。一个样本（不是全部200个！）会帮助而不是冗长。



无论如何，在我看来，这是规范化将会出现的地方



表1：使用ObjectID作为PK存储对象，以及任何其他对象详细信息



表2：共有属性的5列和一个用于ObjectID作为FK



表3：表1中每个对象180行，ObjectID为FK



如果你想要的话，如果table1不会有更多的列，你可以将Table1和Table2结合起来。



这是我能想到你提供的信息和可能有更好的方法。



谢谢

Milind

我在应用程序中使用该场景并将数据列设为varchar。



根据您的编程语言，存储非常简单，或者您需要转换为字符类型。如果是数据时间类型，则在恢复该类型时需要在查询中进行转换。



同样，如果要读取保存的列数据（垂直）成一行，那么你需要一系列内连接。



这里有一条基本规则：一种简化的极其通用的存储方法正如我所描述的，可能需要额外的工作来检索数据。没有免费乘车。检索数据的最简单查询将是您的200列恐怖，每列都是正确的数据类型。如果不添加列，则无法扩展存储的数据。我更喜欢另一个极端：我会编写内部联接块来获取我需要的数据，并且可以在需要时转换数据类型。



。

Is it good way to use more than 200 columns in single Database table? while in one record only 20 columns would have the value and rest 180 will having null value?
Example- Suppose I have 20 Objects each having 15 attributes(Properties) in which 5 attributes are common for all 10 Objects and other 10 attributes are different for all 20 Objects.
Here I defined 5 Columns for Common attributes and what about rest of the 180 attributes? Any one have good idea to store this data with optimum way.

解决方案

An alternative to answer one (which is a good method) is a two-table answer relying upon tagging.

Table 1 has your primary key data.

Table 2 uses a ref to the primary key, a tag, and a value.

The tag is equivalent to a column name. You add as many tagged values as you need for each reference. No need to create a tag type until it's actually used.

The advantage of this type of tagging (instead of columns or normalization) is that it's extensible without adding any additional values to your ref. Values become part of the vocabulary simply because you use the string as a tag.



Clarifications/Examples

Table 1

recid  Your_Main_Data
1      Kittens
2      Roaches

Table 2 (replaces all of the columns)
ref  tag     value
1    pet     domestic
1    taste
2    bug     nasty
2    taste   bitter
2    pet     vermon

So 
column 'ref'   ties in to your main table
column 'tag'   would be like a column header
column 'value' would be the content under your column

Note that only those you need are used.
Since there's not external list for tags, you can use anything at any time.

First, this is too vague. A sample (not all 200 ofcourse !) would have helped rather than verbose.

Anyway, In my opinion, this is where normalization would come into picture

Table1: Store Object with ObjectID as PK, and any other Object details

Table2: 5 columns for common Attributes and one for ObjectID as FK

Table3: 180 rows for each Object in Table 1 with ObjectID as FK

If, you want, you can combine Table1 and Table2 if table1 is not going to have more columns.

This is best I can think with information you have provided and there could be better ways.

Thanks
Milind

I use that scenario in an application and made the data column as a varchar.

Depending upon you programming language, storage is either very easy or you'll need to cast to char types. If it's datatime type, you'll need to do the casting in your query when you recover that type.

Similarly, if you want to read the column data you saved (vertical) into a single row, then you'll need a series of inner-joins.

There's a basic rule in operation here: a simplified extremely generic storage method as I have described may require extra work to retreive the data. There is no free ride. The simplest query to retreive data would be your 200 column horror, each column of the correct data type. You'd have no way to extend the stored data without adding columns. I prefer the other extreme: I'll write the block of inner-joins to get the data I require and can cast the data types at when needed.

.

这篇关于在单个Sql Server表中存储200多个属性值的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！