SQL 2008: Using separate tables for each datatype to return single row

Question

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

Answer 1

I don't think this is a good way to go because:

• A simple insert of 1 record for a contact suddenly becomes n inserts. e.g. if you store varchar, nvarchar, int, bit, datetime, smallint and tinyint data for a contact, that's 7 inserts in datatype specific tables, +1 for the main header record
• Likewise, a query will automatically reference 7 tables, with 6 JOINs involved just to get the full details

I personally think it's better to go for a less "generic" approach. Keep it simple.

Update: The question is, do you really need a flexible solution like this? For contact data, you always expect to be able to store at least a core set of fields (address line 1-n, first name, surname etc). If you need a way for the user to store custom/user definable data on top of that standard data set, that's a common requirement. Various options include:

• XML column in your main Contacts table to store all the user defined data
• 1 extra table containing key-value pair data a bit like you originally talked about but to much lesser degree! This would contain the key of the contact, the custom data item name and the value.

These have been discussed before here on SO so would be worth digging around for that question. Can't seem to find the question I'm remembering after a quick look though!

Found some that discuss the pros/cons of the key-value approach, to save repeating:
http://stackoverflow.com/questions/126271/key-value-pairs-in-relational-database
http://stackoverflow.com/questions/514603/key-value-pairs-in-a-database-table