Implementing custom fields with ALTER TABLE

We are currently thinking about different ways to implement custom fields for our web application. Users sh开发者_如何学Pythonould be able to define custom fields for certain entities and fill in/view this data (and possibly query the data later on).

I understand that there are different ways to implement custom fields (e.g. using a name/value table or using alter table etc.) and we are currently favoring using ALTER TABLE to dynamically add new user fields to the database.

After browsing through other related SO topics, I couldn't find any big drawbacks of this solution. In contrast, having the option to query the data in fast way (e.g. by directly using SQL's where statement) is a big advantage for us.

Are there any drawbacks you could think of by implementing custom fields this way? We are talking about a web application that is used by up to 100 users at the same time (not concurrent requests..) and can use both MySQL and MS SQL Server databases.

---

Just as an update, we decided to add new columns via ALTER TABLE to the existing database table to implement custom fields. After some research and tests, this looks like the best solution for most database engines. A separate table with meta information about the custom fields provides the needed information to manage, query and work with the custom fields.

---

The first drawback I see is that you need to grant your application service with ALTER rights.
This implies that your security model needs careful attention as the application will be able to not only add fields but to drop and rename them as well and create some tables (at least for MySQL).

Secondly, how would you distinct fields that are required per user? Or can the fields created by user A be accessed by user B?

Note that the cardinality of the columns may also significantly grow. If every user adds 2 fields, we are already talking about 200 fields.

Personally, I would use one of the two approaches or a mix of them:

1. Using a serialized field

I would add one text field to the table in which I would store a serialized dictionary or dictionaries:

{ 
  user_1: {key1: val1, key2, val2,...}, 
  user_2: {key1: val1, key2, val2,...}, 
  ...
}

The drawback is that the values are not easily searchable.

2. Using a multi-type name/value table

fields table:

user_id: int
field_name: varchar(100)
type: enum('INT', 'REAL', 'STRING')

values table:

field_id: int
row_id: int # the main table row id
int_value: int
float_value: float
text_value: text

Of course, it requires a join and is a bit more complicated to implement but far more generic and, if indexed properly, quite efficient.

---

I see nothing wrong with adding new custom fields to the database table.

With this approach, the specific/most appropriate type can be used i.e. need an int field? define it as int. Whereas with a name/value type table, you'd be storing multiple data types as one type (nvarchar probably) - unless you complete that name/value table with multiple columns of different types and populate the appropriate one but that is a bit horrible.

Also, adding new columns makes it easier to query/no need to involve a join to a new name/value table.

It may not feel as generic, but I feel that's better than having a "one-size fits all" name/value table.

From an SQL Server point of view (2005 onwards)....
An alternative, would be to store create 1 "custom data" field of type XML - this would be truly generic and require no field creation or the need for a separate name/value table. Also has the benefit that not all records have to have the same custom data (i.e. the one field is common, but what it contains doesn't have to be). Not 100% on the performance impact but XML data can be indexed.