Adding custom attributes to tables

[Brady Kelly]

It came up again today how to handle allowing users to add custom fields to the standard entity schemas delivered in your product's database. I favour actually providing a function that gives the user limited DDL functions, so they can actually add a new, custom field to a table. Another approach is to have a separate table for custom fields, e.g. Customers, and CustomersEx, where only CustomersEx can change, but here updates become trickier than normal. The last and most badass option that we discussed was providing a EAV table, where rows are entity name, field name, field value. Which approach is best?

[i j russell]

EAVs look like a good idea but the practicalities of use (no datatyping, hard to query etc) mean that they should be avoided at all costs. I have only ever seen this problem handled reasonably well once. They created an Entity table and an attributes table for meta data. They then generated the system and custom tables for each entity from the meta data. They then generated views for querying and stored procedures for insert, update and delete. Views and SPs were regenerated as needed when custom attributes were added or removed. The other reason for having custom tables for each entity is that there is a limit of 8k on the total row size in Sql Server (at least prior to 2008).

[Mycroft Holmes]

We are currently using the EAV (what is that by the way) method with an attribute table with AttributeType and AttributeValue. Always varchar of course so yes data typing is an issue. It has allowed us to create a dynamic, extensible solution but there is a definite cost, some of the queries are just horrendous. If I was going to go down the path RJ suggested I would have a customer table with fixed columns and a CustomerEx where the user can add custom columns and the system will rewrite the DDL. I would disallow any custom column from being in the core processing, IE reference only. I'm not sure if this is different from RJs proposal. [edit] IJ not RJ, sorry mate [/edit]

Never underestimate the power of human stupidity RAH