DB Design to store custom fields for a table

Question

Hi All, this question came up based on the responses I got for the question http://stackoverflow.com/questions/2785033/getting-wierd-issue-with-to-number-function-in-oracle

As everyone suggested that storing Numeric values in VARCHAR2 columns is not a good practice (which I totally agree with), I am wondering about a basic Design choice our team has made and whether there are better way to design.

Problem Statement : We Have many tables where we want to give certain number of custom fields. The number of required custom fields is known, but what kind of attribute is mapped to the column is available to the user

E.g. I am putting down a hypothetical scenario below

Say you have a laptop which stores 50 attribute values for every laptop record. Each laptop attributes are created by the some admin who creates the laptop.

A user created a laptop product lets say lap1 with attributes String, String, numeric, numeric, String

Second user created laptop lap2 with attributes String,numeric,String,String,numeric

Currently there data in our design gets persisted as following

Laptop Table
Id Name field1  field2 field3  field4 field5
1  lap1 lappy   lappy  12      13     lappy
2  lap2 lappy2  13     lappy2  lapp2  12

This example kind of simulates our requirement and our design

Now here if somebody is lookinup records for lap2 table doing a comparison on field2, We need to apply TO_NUMBER.

select * from laptop 
where name='lap2'  
and TO_NUMBER(field2) < 15

TO_NUMBER fails in some cases when query plan decides to first apply to_number instead of the other filter.

QUESTIONS
Is this a valid design?
What are the other alternative ways to solve this problem?
One of our team mates suggested creating tables on the fly for such cases. Is that a good idea?
How do popular ORM tools give custom fields or flex fields handling?

I hope I was able to make sense in the question.

Sorry for such a long text..

This causes us to use TO_NUMBER when queryio

Answer 1

If all of the column types are decided at the time the table is created, then generating tables on the fly sounds good to me.

However, if two users are using the same table with different fields, you could create new tables just for the custom fields and join them to the main table. This is more of an object oriented approach.

Answer 2

Could you create an XML graph in the code layer and store it in a SYS.XMLTYPE field type?

http://www.oracle-base.com/articles/9i/XMLTypeDatatype.php

This would allow you to strongly type (in XML) your values and retain meaningful structure.

Answer 3

This is a common scenario with shrink-wrapped apps, where it represents the only opportunity for customizing the data model. But from a purist point of view it is bad practice. Because if a column can contain '27-MAY-2010' or 178.50 or 'Red badger' then clearly it is dependent on something external to the database to give it meaning.

But using an XMLType is even worse because you lose what little structure you have. It becomes difficult to query on the flexible columns. Still there are some scenarios where this is the appropriate solution: mainly when we're not interested in the individual elements, just the collection of properties.

So, what is the best way of dealing with it? Customised functions to go with your custom columns:

SQL> create or replace function get_number
  2      ( p_str in varchar2 )
  3      return number
  4      deterministic
  5  is
  6      return_value number;
  7  begin
  8      begin
  9          return_value := to_number(trim(p_str));
 10      exception
 11          when others then
 12              return_value := null;
 13      end;
 14      return return_value;
 15  end;
 16  /

Function created.

SQL>

We can build a function-based on this column, for performance:

SQL> create index t42_flex_idx on t42 ( get_number( flex_col))
  2  /

Index created.

SQL>

So given this test data ....

SQL> select * from t42
  2  /

        ID FLEX_COL
---------- ------------------------------
         1 27-MAY-2010
         2 138.50
         3 Red badger
         2 23

SQL>

... here's how it works:

SQL> select * from t42
  2  where get_number(flex_col) < 50
  3  /

        ID FLEX_COL
---------- ------------------------------
         2 23

SQL>

Answer 4

Hi Fazal,

This is a common problem and there is no perfect solution. A couple of solutions:

1. Define X fields of type varchar2, Y fields of type number and Z fields of type date. That comes out as potentially 3 times the number of custom fields but you will never have any conversion problem anymore.

Your example would come out like this:

Id Name field_char1  field2_char2 field_char3 ... field_num1 field_num2 ...
1  lap1 lappy        lappy        lappy       ... 12         13     
2  lap2 lappy2       lappy2       lapp2       ... 13         12

In your example you have the same number of numeric values and character values on both rows but it doesn't have to be this way: the third row could have no numeric field for example.

2. Define X fields of type varchar2 and have apply a bijective function to store number or date field (for example Date could be stored as YYYYMMDDHH24miss). You will also need an extra field that will define the context of the row. You would apply the to_number or to_char function only when the rows are of the good type.

Your example:

Id Name context field1  field2 field3  field4 field5
1  lap1 type A  lappy   lappy  12      13     lappy
2  lap2 type B  lappy2  13     lappy2  lapp2  12

You could query the table using DECODE or CASE:

SELECT * 
  FROM laptop
 WHERE CASE WHEN context = 'TYPE A' THEN to_number(field3) END = 12

The second design is the one used in the Oracle Financials ERP (among others). The context allows you to define CHECK constraints with this design (for example CHECK (CASE WHEN context = 'TYPE A' THEN to_number(field3) > 0) to ensure integrity.