将数据保存为 DB 中的 xml 与保存多行数据
我正在设计我的数据库，想知道处理这个问题的最佳方法。我的页面上有如下所示的选项卡：
Tab1
--SubTab1
----Data1
--SubTab1
--SubTab1
Tab2
--SubTab1
--SubTab1
--SubTab1
Tab3
--SubTab1
--SubTab1
--SubTab1
我可以将此信息放入数据库中，该信息将存储在多行中，如下所示
TypeID---------------Name
1--------------------Tab
2--------------------Data
ObjectID----------Parent-------------TypeID
1-----------------0------------------1
2-----------------0------------------1
3-----------------0------------------1
4-----------------1------------------1
或者我可以将其放入数据库中，如下所示：
<root>
<tab name="MyTab">
<tab name="MySubTab">
<data>1234567890</data>
</tab>
</tab>
</root>
如果我仅从数据库中提取 xml，则我不需要选择多行，我只需要选择一行，然后将 xml 解析为一个类，然后将该数据传递给控制器。我只是想知道这是否是一个好主意？如果我的网站将来扩展，我会犯错误吗？随着网站变得更大并需要更多功能，这是否会导致更多维护？
I am designing my database and wanted to know the best way to handle this problem. I have tabs on a page that looks like this:
Tab1
--SubTab1
----Data1
--SubTab1
--SubTab1
Tab2
--SubTab1
--SubTab1
--SubTab1
Tab3
--SubTab1
--SubTab1
--SubTab1
I can put this information in the database that will be stored in multiple rows like this
TypeID---------------Name
1--------------------Tab
2--------------------Data
ObjectID----------Parent-------------TypeID
1-----------------0------------------1
2-----------------0------------------1
3-----------------0------------------1
4-----------------1------------------1
Or I can just put this in the database like so:
<root>
<tab name="MyTab">
<tab name="MySubTab">
<data>1234567890</data>
</tab>
</tab>
</root>
If I pull just the xml from the database then I would not need to select multiple rows i just need to select one row then parse the xml into a class then pass that data to the controller. I just would like to know if this is a good idea? Will I be making a mistake if my site to scale in the future? Will this make more maintenance as the site gets bigger and want more features?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
XML 很难使用 SQL 进行查询。如果您认为永远不需要查询数据，则可以使用 XML。为了将来的灵活性，我可能会将数据存储在多行中。
XML is hard to query using SQL. If you think that you will never need to query the data you could use XML. I'd probably store the data in multiple rows for future flexiblity.
如果您的数据是一大堆 xml，您将无法使用数据库轻松搜索该数据。是否需要在xml中搜索信息？将其分开将使该任务变得更容易。
If your data is one big lump of xml you can't use the database to easily search that data. Do you need to search for information in the xml? Separating it will make that task easier.
也许我误解了它，但似乎您正在决定是否将数据保存为 XML 或将其保存在表中，然后使用 xml 查询以返回它。如果是这种情况：
更容易查询并且可以处理更多更改：如果您将进行很多更改并且数据会以很高的速度增长，那么将数据保存到表中并稍后查询会更好。您可以为复杂的查询提供更多选择。
如果数据增长或变化不大，则速度更快：如果数据不会经常变化，并且不存在增长那么多的危险，那么您可以简单地使用 XML 使其保持正确的语法而且它对性能的影响较小。此方法意味着您在查询正确数据时灵活性较差。
Maybe I misunderstood it but it seems like you are deciding whether to save the data as XML or save it in tables then query it using xml to return it. If that's the case:
Easier to query and can handle more changes: Saving the data to tables and querying it later is better if you will have many changes and it will grow at a high rate. You get more choices for complex querying.
Faster if data doesn't grow or change that much: If the data wont change that often and there is no danger of it growing as much then you can simply use XML to keep it in the right syntax already and it will have less of a performance hit. This method means that you have less flexibility in querying for the right data.
如果您正在考虑是否将 XML 存储在数据库中，那么是时候开始问自己是否有必要将此数据存储在数据库中了。
将这些数据存储在静态 XML 文件中怎么样？这是一个可行的选择吗？为什么以及为什么不呢？
如果此数据不经常更改，并且您不想花时间访问数据库，但仍希望确保将来可以使用 CMS 编辑此数据，那么也可以创建批处理使用数据库中的数据创建静态 XML 文件的过程，然后您的应用程序将完全忽略该数据位于数据库中的事实，而仅使用静态 XML 文件。批处理过程将按计划或按需运行。
对于不经常更改的数据，这是解决此问题的可扩展、可维护且有效的方法。
实现这一目标的类似方法是使用 ASP.net 或 PHP 等服务器端技术从数据库表中生成 XML，然后使用具有较长过期时间的输出缓存来确保生成过程不会经常运行。
根据一般经验，在数据库字段中存储非原始或复杂数据通常不是一个好主意。
If you are considering whether to store XML on the database it is time to start asking yourself whether storing this data on a database is even necessary.
What about storing this data on a static XML file? Is it a viable option? Why and why not?
If this data does not change frequently and you do not want to invest the trip to the database, but you still want to make sure it is possible to edit this data in the future using a CMS, then it is also possible to create a batch process that creates the static XML file using the data in your database and then your application would completely ignore the fact that this data is in the database and just use the static XML file. The batch process would run on a schedule or on-demand.
This is a scalable, maintainable and efficient way to approach this issue for data that doesn't change often.
A similar way to achieve the same is having a server-side technology like ASP.net or PHP generate the XML for you from the database tables and then using output caching with a long expiration time to make sure that the generation process does not run often.
As a general rule of thumb, it is usually not a good idea to store non-primitive or complex data on a database field.
如果使用 SQL Server，我只需使用 层次结构。虽然这可以通过 TSQL 中的 XML 来实现，但也有一些注意事项。最重要的是获取有序的 XML 很糟糕，因为 TSQL (2008) 没有正确/完全支持
position()。它需要对“索引器”列进行有点混乱的连接。 （如果您读回整个 XML blob，则这不适用，但这听起来像是在客户端手动解析令人头疼）。hiearchyid 允许轻松保存这些级别，使数据库保持更标准化的格式（RDBMS 的设计目的是在许多列上高效工作，只需确保具有正确的索引），并且应该比一般来说更容易处理——尤其是。如果你还没有准备好深入 SPROC 领域:-)
hiearchyid 代表父/子关系以及兄弟姐妹的排序（它确实可以整理模型）；它基于深度优先模型，非常适合此类任务。
此外，使用 XML 可能不会节省任何内容（IIRC，[类型] XML 实际上分解为某种内部列设置）。如果需要的话，它（XML）最好用作“输入/输出”消息格式（或“文档”）。
快乐的 SQL 操作。
If using SQL Server, I would just use hierarchyid. While this is possible to do with XML in TSQL, there are several caveats with it. The foremost is getting ordered XML back sucks because TSQL (2008) does not correctly/entirely support
position(). It requires a somewhat messy join to an 'indexer' column. (This doesn't apply if you read the entire XML blob back, but this just sounds like a head-ache to parse manually client-side).hiearchyid allows these levels to be saved easily, leaves the database in a more normalized format (RDBMS' are designed to work efficiently over many columns, just make sure to have the correct indices), and should be much easier to deal with in general -- esp. if you are not ready to dive into SPROC land :-)
hiearchyid represents parent/child relationships as well as ordering of siblings (it can really tidy up the model); it is based on a depth-first model that works well for tasks like this.
Also, using XML will likely save nothing (IIRC, [typed] XML is actually exploded into some kind of internal column setup). It (XML) is much better just to use as a "in/out" message format (or "document"), if required.
Happy SQL'ing.