Hi,

I need an urgent help with the design of the database, We have already a database in production but we are facing a problem with extensibility.

The client information is variant that is
1) number of fields are different for each client
2) client may ask anytime in future to add another field into the table

Please provide your views with practical implication (advantages and disadvantages) or any resource where I can find information.....

Help appreciated.....

one solution may be to have 2 (or more) main tables for your customer, one contains the common information
the other is an extension table containg key pairs
a 3rd table groups and identifes the key pair
table Prod 'existing table
ProdID 'system generated Id
etc....

table ProdExt
ProdID 'fk to table product
KeyID 'fk to table keypair
KeyValue 'contians the required value

table KeyPair
KeyID 'system generated Id
KeyName 'descriptuion of key eg "ProductHeight"
KeyGroupID 'fk in KeyType
SortSeq 'allows you to control the sequence in which the key pair should be presented

table KeyType
KeyGroupID 'system generated Id
KeyGroupDesc ' description eg 'measurements

-you could extend KeyType to include some form of hierarchy

its not necessarily the way I'd want to do it, but it could meet your requirements. I'd want to bottom out why some customers have different information requirements, and try to work out if that difference is because you don't have all the information required at present, or if your teacher wants to find out if you have been awake this last 4 months.

HTH

Thanks for the response
That is right the difference is due to the reason that we do not have all the information required at present.
Moreover, the some customers have different information requirements for example some customers have 3 addresses
may be others have only two. May be later a new customer joins and he needs five addresses. Addresses is just
an example it can be anything which we are not sure right now but these fields will be only informative fields.

The environment is VB6, ADO with SQL Server 2000