Dynamic structure of a table?!!

Hi all,
I have an ER diagram which has an entity called "Form" .This is a typical electronic Form but number of its fields are not known and may vary at future.
What is the best logical design for this case? If I create a table for this Form I should add columns to it in the future.This does not seem nice.Also I do not want to use "Entity-Attribute-Value" method as described at:


Tony Andrews Blog

-Thanks

EAV gives you flexibility and many dynamic applications use it. It is the best approach in terms of application maintenance, you have to code your forms to be data driven.

Quote:

---

EAV gives you flexibility

---

Pardon me? Exactly opposite of all I have heard and seen! That gives you some little flexiblity but screw up you when you want to write new SQL specially if it is a rather complex SQL.
Any idea?
-Thanks for your help

SQL will be complex but you don't have to worry about changing existing code when a new attribute is added. With traditional approach you have to create table for each situation and have to write your code accordingly.

With EAV your application may have to use complex SQL but it is totally data driven and you don't have to worry about how many attributes you need.

Take an example of software like maintenance management system which tracks of maintenance, parts etc of equipments. Rather than having a table (with corresponding attributes) for each type of equipment, you can have equipment table and attributes in child table, this way you don't have to worry about (and create tables beforehand) what types of equipment your customer has and how many attributes each equipment needs. Everything will be data driven.

There are a couple of ways you can find the column names and types dynamically if you want to build sql to reference unknown columns of a table in the furture:

There's the old-fashioned way, along the lines of:

SELECT RTRIM(table_name) table_name,
CHR(44)||trim(column_name) column_name,
CHR(44)||TRIM(data_type)||DECODE(data_type,
'VARCHAR2','('||DATA_LENGTH||')',
'CHAR','('||DATA_LENGTH||')',
'NUMBER',DECODE(DATA_PRECISION,NULL,NULL,
'('||DATA_PRECISION||DECODE(DATA_SCALE,0,NULL,'.'| |DATA_SCALE)||')'),
'DATE',' ', DATA_LENGTH)||CHR(44) dtype
FROM ALL_TAB_COLS
WHERE table_name = 'MYFORM'
ORDER BY table_name, column_id

-- get rid of the print formatting, open it as a cursor in a FOR..LOOP and pull the column names and attributes out in order.

OR, the newfangled way...

up in your stored proc declaration section:
l_desc_tbl DBMS_SQL.DESC_TAB;
l_col_cnt NUMBER := 0;

down in code:

DBMS_SQL.PARSE(l_cursor, i_query, DBMS_SQL.NATIVE);
DBMS_SQL.DESCRIBE_COLUMNS(l_cursor, l_col_cnt, l_desc_tbl);

FOR i IN 1 .. l_col_cnt LOOP
DBMS_SQL.DEFINE_COLUMN(l_cursor, i, l_columnvalue, 4000);

IF (l_desc_tbl(i).col_type = 2) -- Number type.
THEN
IF l_desc_tbl(i).col_scale > 0 -- Has decimals.
THEN
l_desc_tbl(i).col_max_len :=
l_desc_tbl(i).col_precision + 2; -- Add room for +/- and .
ELSE
l_desc_tbl(i).col_max_len :=
l_desc_tbl(i).col_precision;
END IF;
ELSIF (l_desc_tbl(i).col_type = 12) -- Date type.
THEN
l_desc_tbl(i).col_max_len := 8; -- Make dates 8 long.
ELSIF (l_desc_tbl(i).col_type = 8) -- Long type.
THEN
l_desc_tbl(i).col_max_len := 2000;
END IF;
END LOOP;

--------
now you can pull the info put of the l_desc_tbl array and act on it as you wish.

Personally I like to query all_tab_cols directly because I don't have to be on the back-end to do it.

Ciao!