Generic schema design

From: lsllcm <lsllcm_at_gmail.com>
Date: 13 Feb 2006 07:24:16 -0800
Message-ID: <1139844256.158184.49270@g44g2000cwa.googlegroups.com>

Hi All

I have question about schema design, there is one requirement. The system has a application
table, the table has pk column app_id. The user wants to add dynamic attached table for one
specified application. One application has many dynamic tables. One dynamic table can has many records The user can define the column name, type and length for the dynamic table.

The current schema design like the following:

--store the column information
CREATE TABLE ATTACHTABLE
(

APP_ID,
TABLE_NAME,

COLUMN_NAME,
COLUMN_TYPE,
COLUMN_LENGTH

)

--store the value (choice 1)
CREATE TABLE ATTACHTABLE_VALUE
(

APP_ID,
TABLE_NAME,

COLUMN_NAME,
COLUMN_TYPE,
COLUMN_LENGTH,

ROWINDEX,
COLUMN_VALUE
)

--store the value (choice 2)
CREATE TABLE ATTACHTABLE_VALUE
(

APP_ID,
TABLE_NAME,
ROWINDEX,
ROW_VALUE
)

Could you give some suggestion about this kind of design.

Thanks in advance Received on Mon Feb 13 2006 - 09:24:16 CST