Database Design: Is it practical to modify table schema in runtime?

Suppose I have a products table, in which a few fields will be reserved and all other attributes are user generated nullable columns.

 (reserved)
     __
    /  \
   /    \
--------------------------------------
| id | name | color | height | width | 
--------------------------------------

Like EAV, it will allow any number of properties开发者_StackOverflow中文版, but the properties will be queryable as well. What are the potential downsides of this approach?

• Can we rule out security issue if the only thing user controls in ADD/DROP COLUMN statements is field name (which would be always validated to prevent dropping reserved fields)?

• How expensive ADD/DROP COLUMN statements may become when tables grow really large? Assuming we have rate limiting in place to avoid abuse of the system by user.

• How many (nullable, non-indexed) columns are too many for a single table, from performance perspective?

You'd be far better off with a second table with the key/value pairs.

And what makes you think the second table approach wouldn't be queryable?

DDL statements cannot be in a transaction. It would probably depend on the database engine you're using, but I wouldn't be surprised if DDL would have to wait until every other transaction finished, and/or it would block all other transactions while waiting for the other transactions finish. In other words, the performance would suck.

Dynamically modifying a schema at runtime for a relational database is extremely expensive and can usually have some ill effects (not to the point of unleashing things and eating children, but close ;))

So I'd look at two choices.

1. Leave in generic field names of varying types, with a configuration database that maps what the user chose to use these extra fields for so you have proper column headings, etc. in custom reporting processes (I've seen this used in several ERP packages).

2. Consider using a non-relational database that allows storing disparate objects (generally in JSON, etc.)

Instead of adding columns to your existing table, how about creating a second table that contains key-value pairs of attributes with a foreign key to the products table? This will allow for any number and variety of attributes for products, and is easily queryable by joining the tables on the primary key-foreign key.

In production modification of database schemas is a headache. User modification of database schemas is the kind of headache you get when someone runs you over in a truck, then backs up and hits you again.

Information added to the database by users during normal business processing (and this includes names of new attributes) is data, not schema information, and should be treated as such.