Design issue

Anybody have an idea on this.
I currently have a DB with 2 tables.
TableUser
TableInfo
With fields like:
TableUser = UserID, Name, Phone
TableInfo = UserID, comments, status
The user table has the users main info, the info table has some other misc.
info.
I need a way for each user to have a different set of custom fields that
links to the info table.
So I was thinking about adding 2 new tables named TableCustomFields and
TableCustomData.
The TableCustomField would hold the specific users custom fields and have
fields like
CustID
UserID This would link to the induividual user
FieldName This would be the custom field name
DataID This would link to the tableCustomData with the actual data for the
field
The TableCustomData would hold the data for that custom field and have
fields like
CustID This field would link to the field name row
InfoID This filed would link to the info table row
Data This would be the actual data in the custom field
Does this sound right to anybody, or is there a better way to accomplish
what I'm looking for.
I have to keep in mind that I will need to be able to get a users info
table data along with their custom fields and data and display it all in a
row as effieciently as possible.
Thanks for any helpHi,
Since this is a consultation type issue, you can contact Advisory Services
(AS) . Microsoft Advisory Services provides short-term advice and guidance
for problems not covered by Problem Resolution Service as well as requests
for consultative assistance for design, development and deployment issues.
You may call this number to get Advisory Services: (800) 936-5200.
It is hard to say which design will be the best practise for your sceanrio.
You may also combine FieldName and FieldValue in one table in case you need
the query faster and the volumn of Filed is not so high.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.