Hi all

I thought I'd be flexible this time around and let the users decide what contact information the wish to store in their database. In theory it would look as a single row containing, for instance; name, adress, zipcode, Category X, Listitems A.

Example
FieldType table defining the datatypes available to a user:

FieldTypeID, FieldTypeName, TableName
1,"Integer","tblContactInt"
2,"String50","tblContactStr50"
...

A user the define his fields in the FieldDefinition table:

FieldDefinitionID, FieldTypeID, FieldDefinitionName
11,2,"Name"
12,2,"Adress"
13,1,"Age"

Finally we store the actual contact data in separate tables depending on its datatype. Master table, only contains the ContactID

tblContact:

ContactID
21
22

tblContactStr50:

ContactStr50ID,ContactID,FieldDefinitionID,ContactStr50Value
31,21,11,"Person A"
32,21,12,"Adress of person A"
33,22,11,"Person B"

tblContactInt:

ContactIntID,ContactID,FieldDefinitionID,ContactIntValue
41,22,13,27

Question: Is it possible to return the content of these tables in two rows like this:

ContactID,Name,Adress,Age
21,"Person A","Adress of person A",NULL
22,"Person B",NULL,27

I have looked into using the COALESCE and Temp tables, wondering if this is at all possible. Even if it is: maybe I'm only adding complexity whilst sacrificing performance for benefit in datastorage and user definition option.

What do you think?

Best Regards

/Thomas C