MSSQL 2000.

I want to give the user the ability to extend my table. This is what I'm thinking, and I wonder if anyone has any thoughts.

Two tables:
  • User_Columns
  • User_Values

Code:
User_Columns

UC_Guid	RowGuid (for replication, and ease of use).
UC_Table_Name   Table to extend.
UC_Text  	User defined label.
UC_Type 	User defined type - e.g. text box, combo box etc.
UC_Options	User defined, comma delimited, to populate radio boxes, combo boxes etc.
Thinking unique constraint on UC_Table_Name and UC_Text.
Code:
User_Values

UV_Guid		RowGuid.
UV_Tuple_Guid	Points to the Guid in whatever table is extended, so that table can look it up.
UV_UC_Guid	Points to UC_Guid.
UV_Value	Nvarchar to hold the data.
So the view to see all this would be something like (pseudo-sql)
Code:
select * from MyExtendableTable, * from User_Values join on User_Columns
where User_Values.UV_Tuple_Guid = MyExtendableTable.Guid
Obviously that's crappy sql, but I hope you get what I'm thinking.

Then, on the GUI side, you could loop through all the defined custom fields and build the controls/add event handlers on the fly.

This is just an idea, I'm about to write it (there's something wrong, I can smell it), any thoughts are appreciated.

Thanks,
Mike