SQL 2008: Using separate tables for each datatype to return single row
- by Thomas C
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
