I am in the design stage of a new application at the moment, and I have a bit of a design question which I thought I might be able to crowd source some advice with.
我现在正在一个新的应用程序设计阶段，我有一个设计问题，我想我可能会给一些人提供一些建议。
I am connecting to a variety of devices using proprietary connections, each device responds with a collection of data which I parse into a nice class dependent on the machine type. Some machines respond with different data to others, but there is some common ground, for this reason I have a simple inheritance model.
我正在使用专用连接连接连接各种设备，每个设备都用一组数据进行响应，我将这些数据解析为依赖于机器类型的一个很好的类。有些机器对其他机器响应不同的数据，但是有一些共同点，因此我有一个简单的继承模型。
- (abstract) Machine
- Machine Type A
- Machine Type B
- Machine Type C
The "Machine" type has properties such as name, IP address, that sort of thing, which are common to all machines.
“机器”类型具有名称、IP地址之类的属性，这些属性对所有机器都是通用的。
At this point I am coming to design my database, and I do not desire having a different table for each of my classes. Additionally, I want to remain flexible to any new machines which are to be added to the system over time.
此时，我将设计我的数据库，我不希望每个类都有不同的表。此外，我还希望对任何新机器保持灵活性，这些机器将随时间添加到系统中。
So...my idea was to have a table with columns for the common features of any "Machine", and then an XML field containing the rest of the information about a particular machine. This way I figured I could remain flexible without having large levels of redundant "null" values in my database.
所以…我的想法是有一个包含任何“机器”的公共特性的列的表，然后有一个包含关于特定机器的其余信息的XML字段。通过这种方式，我认为我可以保持灵活性，而不必在数据库中拥有大量冗余的“null”值。
This would also keep the column count down, since a machine may have 30 or 40 different features to be recorded.
这也将使列计数降低，因为一台机器可能有30或40个不同的特性要记录。
Since I have never used the XML datatype before, I was wondering if I am going about this problem in a sensible manner? What are the implications of using the XML field.
由于我以前从未使用过XML数据类型，我想知道我是否以一种合理的方式来处理这个问题?使用XML字段的含义是什么?
It is also of note that I would not be likely to be searching by any of the fields in the XML, only on the common fields.
还需要注意的是，我不太可能对XML中的任何字段进行搜索，而只对公共字段进行搜索。
2 个解决方案
#1
2
Depends on how often and how frequently you need to use the data from those custom pieces of data.
取决于您使用这些自定义数据片段的数据的频率和频率。
If you basically need to store the entire XML as a blob, and - if at all - rarely query it, then the XML datatype is a really good option. You can query into it to extract detail info - it's just not the most performant thing to do.
如果您基本上需要将整个XML存储为blob，并且(如果需要的话)很少查询它，那么XML数据类型是一个非常好的选择。你可以通过查询来提取细节信息——这并不是最有效的方法。
If that specific data is the heart and soul of your app and you need to use and query that all the time, then I'd probably just create the necessary relational tables and store it that way.
如果这个特定的数据是你的应用程序的核心和灵魂，你需要一直使用和查询，那么我可能只需要创建必要的关系表，并将其存储起来。
The XML data type is great
XML数据类型很棒
-
if you need that data in XML format most of the time (e.g. you get it from some source, need to store it and possibly forward it to some other place)
如果您大多数时候都需要XML格式的数据(例如，您从某个源获取数据，需要存储数据并可能将其转发到其他地方)
-
if you need to store data that's not very often queried and used
如果需要存储不经常查询和使用的数据
The XML data type in SQL Server works very well - but again: querying against it is possible, but rather slow (compared to querying relational tables).
SQL Server中的XML数据类型工作得非常好——但同样:针对它进行查询是可能的，但相对于查询关系表而言，查询速度比较慢。
#2
3
personally, i suggest you avoid storing XML in the database.. i used this approach once using Oracle 10g on a powerful server.. it's fine to begin with but once you start querying your data, filtering on values from the XML performance begins to plummet.. yes, you can get deeper and deeper into the configuration of XMLDB but it might begin to drive you mad..
我个人建议您避免在数据库中存储XML。我曾经在一个强大的服务器上使用过Oracle 10g。一开始是可以的，但是一旦开始查询数据，对XML性能中的值进行过滤就会开始急剧下降。是的，您可以对XMLDB的配置进行更深入的了解，但它可能会开始让您疯狂。
try reading some of the other questions on storing arbitrary data first, e.g. Storing Scientific Data in a Relational Database.
尝试先阅读一些关于存储任意数据的其他问题，例如，在关系数据库中存储科学数据。
It boils down to having a good think about how you plan to use, extract and query the data.
它可以归结为要好好考虑如何使用、提取和查询数据。
#1
2
Depends on how often and how frequently you need to use the data from those custom pieces of data.
取决于您使用这些自定义数据片段的数据的频率和频率。
If you basically need to store the entire XML as a blob, and - if at all - rarely query it, then the XML datatype is a really good option. You can query into it to extract detail info - it's just not the most performant thing to do.
如果您基本上需要将整个XML存储为blob，并且(如果需要的话)很少查询它，那么XML数据类型是一个非常好的选择。你可以通过查询来提取细节信息——这并不是最有效的方法。
If that specific data is the heart and soul of your app and you need to use and query that all the time, then I'd probably just create the necessary relational tables and store it that way.
如果这个特定的数据是你的应用程序的核心和灵魂，你需要一直使用和查询，那么我可能只需要创建必要的关系表，并将其存储起来。
The XML data type is great
XML数据类型很棒
-
if you need that data in XML format most of the time (e.g. you get it from some source, need to store it and possibly forward it to some other place)
如果您大多数时候都需要XML格式的数据(例如，您从某个源获取数据，需要存储数据并可能将其转发到其他地方)
-
if you need to store data that's not very often queried and used
如果需要存储不经常查询和使用的数据
The XML data type in SQL Server works very well - but again: querying against it is possible, but rather slow (compared to querying relational tables).
SQL Server中的XML数据类型工作得非常好——但同样:针对它进行查询是可能的，但相对于查询关系表而言，查询速度比较慢。
#2
3
personally, i suggest you avoid storing XML in the database.. i used this approach once using Oracle 10g on a powerful server.. it's fine to begin with but once you start querying your data, filtering on values from the XML performance begins to plummet.. yes, you can get deeper and deeper into the configuration of XMLDB but it might begin to drive you mad..
我个人建议您避免在数据库中存储XML。我曾经在一个强大的服务器上使用过Oracle 10g。一开始是可以的，但是一旦开始查询数据，对XML性能中的值进行过滤就会开始急剧下降。是的，您可以对XMLDB的配置进行更深入的了解，但它可能会开始让您疯狂。
try reading some of the other questions on storing arbitrary data first, e.g. Storing Scientific Data in a Relational Database.
尝试先阅读一些关于存储任意数据的其他问题，例如，在关系数据库中存储科学数据。
It boils down to having a good think about how you plan to use, extract and query the data.
它可以归结为要好好考虑如何使用、提取和查询数据。