Hi Tom

We are trying to design our database to accomodate dynamic alteration of our schema by providing
user defined columns. The client should be able to add columns to tables without altering the base
schema. Example
If we have a customer table with the example data as follows:

Cust_ID 100
Cust_Name Corrie & Walter Inc
Cust_Contact Scott

Since we dont at this time what other attributes might be required during the implementation we
provide a Cust_Extended table where we define the specific attributes such as follows :

Attribute_ID 1001
Cust_ID 100
Attribute_Value 94041

Also we have a User_defined_Attributes table as follows

Attribute_ID 1001
Atrr_Name ZipCode
Attr_Datatype Number

So in this table we can add all the user defined columns that need to go in the Cust_Extended table.

The data that would go in such tables would be transaction data in millions of records.

My question is as follows:
Is this a good design approach as this involves a lot of transaction data. If I have to search on these tables using joins on them wouldnt it be extremely slow?

For Example

Find the customer whose zipcode=94041 or Company='Compaq'

select cust_id from Cust_Extended where
attribute_value in ('94041','Compaq')
And Attribute_id in
(select Attribute_id from User_defined_Attributes where
atrr_name in ('ZipCode','Company'));



Schema source:


Create table customer
(Cust_ID number,
Cust_Name varchar2(34),
Cust_Phone varchar2(50));

Create table Cust_Extended
(Attribute_ID number,
Cust_ID number,
Attribute_Value varchar2(64));

Create table User_defined_Attributes
(Attribute_ID number,
Atrr_Name varchar2(54),
Attr_Datatype varchar2(32));

insert into Customer
Values(100,'Corrie', '6501111111');
insert into Customer
Values(200, 'Scott', '4087879999');
insert into Customer
Values(300,'Smith', '4153338899');

insert into User_defined_Attributes
Values(1001,'ZipCode','Number');
insert into User_defined_Attributes
Values(1002,'Company','varchar');
insert into User_defined_Attributes
Values(1003,'Region','varchar');


insert into Cust_Extended
Values(1001,100,'94041');
insert into Cust_Extended
Values(1002,100,'Microsoft');
insert into Cust_Extended
Values(1001,200,'Compaq');
insert into Cust_Extended
Values(1001,300,'HP');



Thanks
Tanu