Results 1 to 5 of 5

Thread: Dynamic structure of a table?!!

  1. #1
    Join Date
    Jul 2005
    Posts
    35

    Question 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

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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.

  3. #3
    Join Date
    Jul 2005
    Posts
    35
    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

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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.

  5. #5
    Join Date
    Dec 2005
    Posts
    2
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •