关系数据库中自定义字段的设计模式 [英] Design Pattern for Custom Fields in Relational Database

问题描述

我已经分配了一个任务来创建（相对）简单的报告系统。在这些系统中，用户将显示报表的表结果。一个表有一些字段，每个字段给每个记录中的用户提供一些信息。但是我的问题是，每个报告字段都不会被开发人员声明。它必须由系统的用户声明。所以我的报表是动态的。

我在

在上图中，我存储每个记录的报告在报告表中。另外我将 ReportType 中的报告类型存储起来。对于将在报告记录中使用的每个字段，我将使用 ReportFieldValue 。字段类型将被存储在 ReportField 中。

所以如果我要首先添加一个记录到我的数据库添加一行到报告表。然后对于每个添加的记录字段，我将在 ReportFieldValue 表中添加一行。

但是，您可能会注意到，在这些方法中，我必须将每个字段值存储在char（255）中。问题是像 datetime 类型的字段类型，不应该作为字符串存储。这种类型的系统是否有任何设计模式或架构？

解决方案

通过将 VALUE 替换为<$ c来避免字符串类型的数据$ c> NUMBER_VALUE ， DATE_VALUE ， STRING_VALUE 。这三种类型在大部分时间都很好。
如果需要，您可以稍后再添加XMLTYPE和其他花哨的列。对于Oracle，使用VARCHAR2而不是CHAR来节省空间。

始终尝试将值存储为正确的类型。本地数据类型更快，更小，更易于使用，更安全。

Oracle具有通用数据类型系统（ANYTYPE，ANYDATA和ANYDATASET），但这些类型很难使用，在大多数情况下应该避免使用。

建筑师经常认为对所有数据使用单个字段可以使事情更容易。它使得更容易生成数据模型的漂亮图片，但它使得每个
更困难。考虑这些问题：

1. 在不知道类型的情况下，您无法对数据进行任何有趣的事情。即使显示数据，知道类型来证明文本是有用的。在99.9％的
   用例中，对于3位列中的哪一列来说，这是很明显的。



2. 针对字符串型数据开发类型安全的查询是痛苦的例如，假设您想为本千年出生的人找到出生日期：

     select * 
   从ReportFieldValue 
   加入ReportField 
   在ReportFieldValue.ReportFieldid = ReportField.id 
   其中ReportField.name ='出生日期'
   和to_date（值，'YYYY-MM-DD '）>日期'2000-01-01'
     

   你能发现错误吗？上述查询是危险的，即使您以正确的格式存储日期，很少有开发人员知道如何正确地修复它。 Oracle具有优化，难以强制执行特定的操作顺序。您需要这样的查询才能安全：

    从
   （$ b）中选择* 
    $ b选择ReportFieldValue。*，ReportField。* 
    --ROWNUM通过阻止视图合并和谓词推送来确保类型安全。
   ，rownum 
   从ReportFieldValue 
    join ReportField 
   在ReportFieldValue.ReportFieldid = ReportField.id 
   其中ReportField.name ='出生日期'
   ）
   其中to_date（值，'YYYY-MM-DD'）> date'2000-01-01'; 
     

   您不需要告诉每个开发人员以这种方式写入他们的查询。 >

I have assigned a task to create (relatively) simple reporting system. In these system, user will be shown a table result of report. A table has some fields and each field give some part of information to user in each record. My problem however is that each report field will not be declared by developer. It must be declared by user of system. So my reports table are dynamic.

I saw example in 'Data Driven Custom View Engine in ASP.NET MVC' for creating dynamic forms using Asp.net MVC Framework but I don't know that is appropriate for my system or not.

Update1:

Currently I ended with following Entity Relationship Diagram:

In above diagram, I store every record for report in Report table. Also I store type of report in ReportType. For each field that will be used in report record I will use a ReportFieldValue. Type of fields will be stored in ReportField.

So If I want to add a record to my db first I add a row to Report Table. Then for each added record fields I will add a row to ReportFieldValue table.

However as you may notice, in these approach I must store every field value in char(255). The problem is for fields type like datetime that should not be stored as string. Is there any design pattern or architecture for this type of systems?

解决方案

Avoid stringly-typed data by replacing VALUE with NUMBER_VALUE, DATE_VALUE, STRING_VALUE. Those three types are good enough most of the time. You can add XMLTYPE and other fancy columns later if they're needed. And for Oracle, use VARCHAR2 instead of CHAR to conserve space.

Always try to store values as the correct type. Native data types are faster, smaller, easier to use, and safer.

Oracle has a generic data type system (ANYTYPE, ANYDATA, and ANYDATASET), but those types are difficult to use and should be avoided in most cases.

Architects often think using a single field for all data makes things easier. It makes it easier to generate pretty pictures of the data model but it makes everything else more difficult. Consider these issues:

1. You cannot do anything interesting with data without knowing the type. Even to display data it's useful to know the type to justify the text. In 99.9% of all use cases it will be obvious to the user which of the 3 columns is relevant.

2. Developing type-safe queries against stringly-typed data is painful. For example, let's say you want to find "Date of Birth" for people born in this millennium:

   select *
   from ReportFieldValue
   join ReportField
       on ReportFieldValue.ReportFieldid = ReportField.id
   where ReportField.name = 'Date of Birth'
       and to_date(value, 'YYYY-MM-DD') > date '2000-01-01'
   

   Can you spot the bug? The above query is dangerous, even if you stored the date in the correct format, and very few developers know how to properly fix it. Oracle has optimizations that make it difficult to force a specific order of operations. You'll need a query like this to be safe:

   select *
   from
   (
       select ReportFieldValue.*, ReportField.*
           --ROWNUM ensures type safe by preventing view merging and predicate pushing.
           ,rownum
       from ReportFieldValue
       join ReportField
           on ReportFieldValue.ReportFieldid = ReportField.id
       where ReportField.name = 'Date of Birth'
   )
   where to_date(value, 'YYYY-MM-DD') > date '2000-01-01';
   

   You don't want to have to tell every developer to write their queries that way.

这篇关于关系数据库中自定义字段的设计模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！