Custom Columns

[Mike Hildner]

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

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.

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)

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

[dRAMmer]

something like:

SELECT x.*, v.UV_Value, c.UC_Text, c.UC_Type, c.UC_Options
FROM MyExtendableTable x 
          JOIN User_Values v ON v.UV_Tuple_Guid = x.Guid
          JOIN User_Columns c ON c.UC_Guid = v.UV_UV_Guid
WHERE c.UC_Table_Name = YourFilteredTable