Storage of varying data types in SQL

[Gregory Wexler]

re: [Windows 2000 SP1, SQL Server 7.0 SP2]

I am developing an online web-based address book for multiple users. There are STANDARD FIELDS and CUSTOM FIELDS.

Standard fields include: Name,Street,City,State,Zip.
Custom fields are those defined by a specific user. For example:

User-A Custom fields:
Interest Rate <real>
Loan Amount <currency>
Start date <date>

User-B Custom fields:
Blood type <char 3>
Date of birth <date>
Referred by <varchar 50>

Different users can have different custom fields in their address book. As you can see, while the standard fields for each user can be

stored in a single table. However, I have several methods by which I can store the CUSTOM fields.

------------------------------------------------
Method 1: Create 2 separate tables called CustomField and CustomValue:

CustomField has fields:
FieldID <int>
FieldName <varchar 25>
UserID <int>

CustomValue has fields:
ValueID <int>
Value <varchar 50>
FieldID <int>

------------------------------------------------
Method 2: Create a separate Field and multiple Value tables for each data type:
CustomField, CustomCharValue, CustomIntValue, CustomMoneyValue, etc...

CustomField has fields:
FieldID <int>
FieldName <varchar 25>
FieldType <smallint> (determines which TABLE, below, contains the data)
UserID <int>

CustomCharValue
CharValueID <int>
IntValue <Varchar 50>
FieldID <int>

CustomIntValue
IntValueID <int>
IntValue <int>
FieldID <int>

etc....etc...


The structures of those tables would be similar to Method 1, but the data would be segregated based on their data type.

--------------------------------------------------

I&#39;m thinking that while Method 1 will be easier to implement, Method 2 may offer me better performance if coded correctly. I&#39;m going

to assume that I&#39;ll have at least 1-5 million records to work with over the course of my first year and I will need the ability to sort

records based on values in the custom fields as well.

My first question is: Which method should I be considering and is there an alternative or hybrid that I should be considering?

My second question is: What statements should I use in my stored procedure that will enable me to retrieve a list of USERID, CustomFieldIDs and their values as one resulting table that I can query at will and with solid performance?

Gregory
email: sqlGuy@clubtel.com

[Bill Lee]

You might consider having custom field and value in one table. Even though the custom field name column is denormalized (i.e. you have redundant info), that&#39;s one big table you no longer have to perform a join on. I&#39;ve seen custom folders implemented this way with very good performance.


------------
Gregory Wexler at 6/2/01 11:40:35 AM

re: [Windows 2000 SP1, SQL Server 7.0 SP2]

I am developing an online web-based address book for multiple users. There are STANDARD FIELDS and CUSTOM FIELDS.

Standard fields include: Name,Street,City,State,Zip.
Custom fields are those defined by a specific user. For example:

User-A Custom fields:
Interest Rate <real>
Loan Amount <currency>
Start date <date>

User-B Custom fields:
Blood type <char 3>
Date of birth <date>
Referred by <varchar 50>

Different users can have different custom fields in their address book. As you can see, while the standard fields for each user can be

stored in a single table. However, I have several methods by which I can store the CUSTOM fields.

------------------------------------------------
Method 1: Create 2 separate tables called CustomField and CustomValue:

CustomField has fields:
FieldID <int>
FieldName <varchar 25>
UserID <int>

CustomValue has fields:
ValueID <int>
Value <varchar 50>
FieldID <int>

------------------------------------------------
Method 2: Create a separate Field and multiple Value tables for each data type:
CustomField, CustomCharValue, CustomIntValue, CustomMoneyValue, etc...

CustomField has fields:
FieldID <int>
FieldName <varchar 25>
FieldType <smallint> (determines which TABLE, below, contains the data)
UserID <int>

CustomCharValue
CharValueID <int>
IntValue <Varchar 50>
FieldID <int>

CustomIntValue
IntValueID <int>
IntValue <int>
FieldID <int>

etc....etc...


The structures of those tables would be similar to Method 1, but the data would be segregated based on their data type.

--------------------------------------------------

I&#39;m thinking that while Method 1 will be easier to implement, Method 2 may offer me better performance if coded correctly. I&#39;m going

to assume that I&#39;ll have at least 1-5 million records to work with over the course of my first year and I will need the ability to sort

records based on values in the custom fields as well.

My first question is: Which method should I be considering and is there an alternative or hybrid that I should be considering?

My second question is: What statements should I use in my stored procedure that will enable me to retrieve a list of USERID, CustomFieldIDs and their values as one resulting table that I can query at will and with solid performance?

Gregory
email: sqlGuy@clubtel.com