mysql - Design Pattern for Custom Fields in Relational Database

Question

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?

Answer 1

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.