Re: Implementing different document types with different attributes

Implementing different document types with different attributesHi!

FlexFields are used in Oracle E-Business Suite, they aren't nothing else than varchar2 fields where you can configure Apps to write your custom data (either one logical field by database column, or several fields separated by colon).

For your case, if you add 70 varchar2 or number columns to your table, these columns which have null values will take only 1 byte (for column length which will be 0). Also, if you have let say 20 last columns as NULL for particular row, then even this 1 byte for last 20 null columns is omitted (column count for row is decreased instead). But as soon as you got some non-null value in your last colum of row, then all columns have to be stored in row.

You could add a column doctype (which you probably already have), use few attribute columns and use decode or some application side construct to translate appropriate columns to appropriate attribute values for current doctype.

Let say I got doc types: doc mp3 zip

For all types ATTR1 stores document size in bytes, but ATTR2 is word count for doc, duration in seconds for mp3 and uncompressed size for zip:

And table contains:

ID | DOCTYPE | ATTR1 | ATTR2 |
 1 | doc     | 154332| 8850  |
 2 | mp3     | 128000|   16  |
 3 | zip     |  32768| 55980 |

And query or application takes doctype into account:

select attr1 as bytes, decode(lower(doctype),

'doc', 'Word Count:',
'mp3', 'Minutes:',
'zip', 'Uncompressed size:',
'Unspecified') as custom,

    ATTR2
from my_table;

Also, you can combine boolean values to number or varchar and use bitand function or substr function depending on data type. Also, function based indexes come handy when your want to index these columns...

Tanel.

• Original Message ----- From: Vernaillen Tim To: Multiple recipients of list ORACLE-L Sent: Thursday, August 07, 2003 4:34 PM Subject: Implementing different document types with different attributes

  Hello

  I've an installation/implementation question!   We've to analyse 50 document types, in total those 50 has 70 different attributes.   We don't want to put all those document types into one table, because more than the half (35) of the attributes are not always used for each document type. This will have to much disk space for each record, if most of the fields are just blank.

  Has anyone suggestions how to build our table-structure?   I've heart something about FlexFields, what are they?   Takes every field diskspace, even if it's blank (null)?

  Thanks in advance for the response!

  Tim

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).