使用 XML 字段 Vs.为不稳定的组织创建一个表
我正在设计一个为组织开具和存储发票的应用程序。问题是这个组织根本不稳定。发票的种类很多，并且可能会增加和变化。
首先，我尝试在 DAL 中使用表，一张表用于存储发票，一张表用于发票字段，一张表用于发票字段值。问题是，这种方式需要反射来稍后检测字段，当发票包含大量项目时，这可能会减慢应用程序的速度。
其次，我尝试将发票数据的核心保留为两个表：发票和发票项目。其他字段完全可以从其他表中捕获。我的意思是，业务层应该为每种发票类型提供不同的结果。它应该选择正确的查询，并根据发票类型处理结果。此解决方案的两个问题是：
当我想显示时，其他表之间仍然有很多连接 每个发票项目的发票。我应该重新计算一切 每次对于每个发票项目。
如果发票项目表不支持新类型怎么办？ 发票？然后我可能应该添加一个新表来存储此类发票项目。
我的客户要求我保留与发票相关的所有数据，他们确实这样做了 不想让我每次都重新计算。他们想要像 创建发票时相关数据的快照。
现在，我想我可以在表中使用 XML 的好处吗？我可以将发票保存为 xml 格式的任何字段。
- 我可以保存他们的发票的不同版本。
- 对于更改，我只更新 Business.dll，不需要更改 DAL。
- Linq to XML 并不慢。
你有什么建议？
I am in the middle of design an application to issue and store invoices for an organization. The problem is the organization is not stable at all. There are many types of invoices and they may increase and change.
First, I tried to use tables in my DAL, one table to store invoices, one for invoice fields, and one for invoice field values. The problem was, this way needs Reflection to detect fields later, and this may slow the application when invoice contains plenty items.
Second, I tried to keep core of invoice data as two tables: invoices and invoice items. Other fields are fully catchable from other tables. I mean, Business Layer should provide different results per invoice type. It should choose the right queries, and process the results according to the type of invoice. Two problems with this solution are:
I still have lots of join between other tables when I want to show
an invoice, for per invoice item. I should recalculate everything
every time for every invoice item.What if the invoice items table doesn’t support a new type of
invoice? Then I probably should add a new table to store that kind of invoices items.My client asked me to keep all data related to an invoice, they do
not want me to recalculate it every time. They want something like a
snapshot of related data at the time invoice is creating.
Now, what I’m think can I use the benefit of XML in table? I can save the invoice, with whatever fields as xml.
- I can save the different version of their invoices.
- On changes, I only update my Business.dll and no DAL change required.
- Linq to XML is not slow.
What do you suggest?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我们已经使用这种方法几年了，但与 nhibernate 结合使用。
它非常适合网络商店，并且您必须修改数据层。但是您可以使用新的模型定义创建自定义项目（IoContainer 通常有助于解决依赖关系）。
非常适合将重要数据分入单独的列，而将其余动态内容分入一个 XML 列。所有查询必须仅适用于表列（以获得最佳性能）。为了获取其余数据，我们有一个实体基类，它在后台将数据序列化和反序列化到其余的“虚拟”字段中。
为了进一步提高应用程序性能，我们使用内部搜索器/爬虫，它返回数据的速度比与数据库通信的速度快得多。
因为 nhibernate 不支持（原生）linq - 现在我正在寻找一个替代的、已经实现的实体框架变体。
如果有人有信息要分享 - 拜托，会很高兴看到并分享。检查（并与我们的变体进行比较 - 选择更好的一个）...
we are using such approach already few years, but in combination with nhibernate.
it's work perfect for web stores, and right you have to modify data layer. But you can create a custom project, with new model definition (IoContainer usual helps to resolve dependencies).
Ideal to sore important data into separate columns, but rest, dynamic content - into one XML column. All queries have to work only with table columns (for best performance). To obtain rest data we have an entity base class which in background serialize and de-serialize data into the rest of the "virtual" fields.
To increase application performance even more, we are using internal searcher/crawlers, which returns data much faster than communication with database.
Because nhibernate doesn't support (natively) linq - right now i am looking for an alternative, already implemented variant for entity framework.
If somebody have information to share - please, will be good to look & check (& compare with our variant - to choose better one)...
发票系统必须对发票及其所有关系进行快照，这是很常见的。原因是公司变动。例如：
发票始终必须显示系统状态中的数据发票创建日期。您可以通过对发票和发票项目表进行某种程度的非规范化或通过大量与发票相关的每条记录来实现这一点。
是的，您可以，但对于实体框架，该字段只是一个字符串，因为它不理解 SQL XML 数据类型。
Linq-to-XML 并不重要，因为 Linq-to-XML 是用于查询加载的 XML 文档的 .NET 功能。它将查询存储在 SQL Server 中的 XML 文档，除非您将所有发票从 SQL Server 加载到应用程序并在应用程序的内存中进行 Linq-to-XML 查询。这很慢，实际上这是你的应用程序的杀手。
要在 SQL Server 中查询 XML 数据，您必须使用本机 SQL 及其 XML 功能（XPath、XQuery）。对于 SQL 人员来说，XML 和查询 XML 将如何影响查询的整体性能以及它在多大程度上适合您的需求是一个问题。
几乎没有。您还必须更新 DAL，因为查询 XML 的所有 SQL 查询都将放置在那里。除非您有一些非常复杂的数据结构来配置新发票类型的应用程序（包括完整的 UI 配置，因为新字段可以有新的规则和验证，它可以是从其他数据源填充的组合框等），否则您必须也更新用户界面。
你有什么选择？
前两种情况都无法解决新发票类型可能遇到的所有问题。例如，如果新字段与其他字段存在某种关系依赖关系、高级业务验证或需要一些外部数据，那么实际上将无法解决。
建立接受他们创建的任何类型发票的完全通用系统是可能的，但该系统的复杂性和价格至少会高出 10 倍。它还会影响提交申请的时间。复杂性也会影响可用性，因为应用程序需要非常仔细的用户体验设计才能易于使用。
这是项目管理中的典型失败，其中需求价格没有正确传达，因此项目的期望远远大于预算允许的范围。此外，为没有优化业务流程并且只是遵循混乱的业务开发项目也是一场噩梦。
我看到一些项目，期望现在拥有一个应用程序，该应用程序将服务（无需任何额外的开发）所有可能的未来案例，而不知道如何定义这些案例。当然，预算总是足以交付当前已知的案例。所有这些项目都未能满足这一要求。
顺便提一句。为了确保您涵盖尽可能多的案例，您不应该从一开始就使用所有当前的发票类型。只需让您的分析师为每种发票类型收集最少的字段集并使用它们即可。所有真实发票类型都必须通过您的系统进行配置，而不是硬编码。
It is common that invoice system must take snapshot of invoice and all its relations. The reason are changes in company. For example:
Invoice always must show data in the system state from the date the invoice was created. You can achieve that either by some level of denormalization for invoice and invoice items tables or by massive of every record related to invoice.
Yes you can but for entity framework this field will be just a string because it doesn't understand SQL XML data type.
Linq-to-XML doesn't matter because Linq-to-XML is .NET feature for querying loaded XML document. It will querying XML documents stored in SQL server unless you load all invoices from your SQL server to your application and make Linq-to-XML query in memory of your application. That is slow, actually that is killer of your application.
To query XML data in SQL server you must use native SQL with its XML features (XPath, XQuery). It is question for SQL guys how will XML and querying XML affect overall performance of your queries and how much does it suite to your needs.
Hardly. You will have to update your DAL as well because all your SQL queries querying your XMLs will be placed there. Unless you will have some very complex data structure for configuring your application for new invoice type (including complete UI configuration because new fields can have new rules and validations, it can be combo boxes filled from other data source, etc.) you will have to update UI as well.
What are your options?
Neither of former cases will solve all problems you can have with new invoice type. For example if new field is some relational dependency on other field, advanced business validation or requires some external data it will really not be solved.
Heaving fully universal system accepting any type of invoice they ever create is possible but the complexity and price of that system will be minimally 10x higher. It will also affect time to deliver the application. The complexity will also have impact on the usability because application will need very careful UX design to be easy to use.
This is typical failure in project management where price of requirements is not communicated correctly and so the expectations from the project is much bigger than budget allows. Also developing the project for business which doesn't have optimized business processes and just follows chaos is nightmare.
I saw projects where expectations was to have one application now which will serve (without any additional development) all possible future cases without knowing how these case will be defined. Sure the budget was always just enough to deliver currently known cases. All these projects failed this requirement.
Btw. to make sure that you covered as much cases as you can you should not work with all your current invoice types from start. Just let your analyst to collect minimal set of fields for every invoice type and work with them. All real invoice types must be configured through your system instead of hardcoded.