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

问题描述

我已经分配了一个任务来创建（相对）简单的报告系统。在这些系统中，用户将显示报告的一个表的结​​果。表有一些领域和各个领域给予每个记录信息，用户的一些部分。然而，我的问题是，每个报表字段不会被开发商声明。它必须由用户系统声明。因此，我的报告表是动态的。

我看到例如数据驱动的自定义视图引擎ASP.NET MVC 创建使用Asp.net MVC框架动态表单，但我不知道这是适合我的系统或没有。

UPDATE1：

目前我有以下实体关系图结束

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

所以，如果我想将记录添加到我的分贝首先我行添加到报告表。然后，每增加记录的字段我将添加一个行 ReportFieldValue 表。

不过，你可能会注意到，在这些方法，我必须存储在CHAR（255）的每个字段的值。问题是字段中键入像日期时间不应该被存储为字符串。是否有这种类型的系统中的任何设计图案或​​结构？

解决方案

与 NUMBER_VALUE 值避免stringly类型的数据$ C>， DATE_VALUE ， STRING_VALUE 。这三种类型是足够好的大部分时间。
  如果在需要的，他们可以在以后添加XMLTYPE等花哨列。而对于甲骨文，使用VARCHAR2代替CHAR以节省空间。

应尽量将存储值正确的类型。本机数据类型是更快，更小，更容易使用，并且更安全。

Oracle有一个通用的数据类型系统（ANYTYPE，ANYDATA和ANYDATASET），但这些类型难以使用并且应该在大多数情况下可避免

建筑师常常想使用单个领域的所有数据，使事情变得更容易。这使得它更容易产生数据模型的pretty图片，但它使一切
 其他更困难。考虑这些问题：

1. 您不能做任何有趣的数据不知道类型。即使显示的数据是非常有用的知道要对齐文本类型。在所有的99.9％
   使用情况下，它是显而易见的3列的是相关的用户。



2. 针对stringly类型的数据开发类型安全的查询是痛苦的。例如，假设你想找到出生日期对于出生在这个千年的人：

     SELECT *
   从ReportFieldValue
   加入ReportField
       在ReportFieldValue.ReportFieldid = ReportField.id
   其中，ReportField.name =出生日期
       和TO_DATE（价值，'YYYY-MM-DD'）＆GT;日期2000-01-01
    

   你能发现的bug？上面的查询是危险的，即使你存储在正确格式的日期，只有极少数开发者知道如何正确地解决它。 Oracle有优化，使其难以强制操作的具体顺序。你需要一个这样的查询是安全的：

     SELECT *
   从
   （
       选择ReportFieldValue *，ReportField。*
           --ROWNUM确保类型由preventing视图合并和predicate推动安全。
           ，ROWNUM
       从ReportFieldValue
       加入ReportField
           在ReportFieldValue.ReportFieldid = ReportField.id
       其中，ReportField.name =出生日期
   ）
   其中，TO_DATE（价值，'YYYY-MM-DD'）＆GT;日期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屋！