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?
A design pattern, or design solution, or simply design, is a response to a problem. The structure of a pattern is based (a) on the traditional pattern structure as delivered by Gamma et al [GHJV95] and (b) on the fundamentals of everyday operations around a database system.
Custom Fields are any fields not already present in Agile CRM. Businesses use custom fields to track extra contact details and map additional fields from Web Forms or CSV files (when importing/exporting contacts or companies). Note that only system administrators can add/modify custom fields in Agile CRM.
Custom fields are metadata categories that allow to fully describe the type of assets a business manager is storing in a digital library.
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:
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With