Custom Columns

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

something like:

Code:

---

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

---