Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-06-03 : 13:34:14
|
Gregory writes "I've updated my previous question:
[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
Loan Amount
Start date
User-B Custom fields:
Blood type
Date of birth
Referred by
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
FieldName
UserID
CustomValue has fields:
ValueID
Value
FieldID
------------------------------------------------
Method 2: Create a separate Field and multiple Value tables for each data type:
CustomField, CustomCharValue, CustomIntValue, CustomMoneyValue, etc...
CustomField has fields:
FieldID
FieldName
FieldType (determines which TABLE, below, contains the data)
UserID
CustomCharValue
CharValueID
IntValue
FieldID
CustomIntValue
IntValueID
IntValue
FieldID
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'm thinking that while Method 1 will be easier to implement, Method 2 may offer me better performance if coded correctly. I'm going
to assume that I'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?
Gregory
email: sqlGuy@clubtel.com" |
|
|
|
|
|
|
|
UPDATED: Theory: Storage of varying data types