Why Using String Keys Is Generally a Bad Idea

Repeating a variable length string over many rows (both data and indexes) is far less efficient than storing a tinyint value.

• The string "Fairy Tale" takes 12 bytes at least on most systems including 2 for length.
• You have denormalised and added data modification anomaly risk
• You then have case sensitivity and collation to take into account for comparisons

Edit:

Your main problem is database size and bloating because rows are longer then necessary. This means less rows per page and more memory use for queries. See these for why

• https://dba.stackexchange.com/q/4968/630
• https://stackoverflow.com/a/5654947/27535

I've seen huge databases that don't use lookup tables (designed by Hibernate ORM on MySQL) and have long strings repeated. By my estimate, the database could have been 60% smaller at least.

Normalisation isn't an issue if you are using lookup table on the natural key. Which you have clarified

When you start getting to "user defined fields" as is often found in bug trackers, customer resource management, and similar business tools is that they are not backed with a table with a bajillion fields (if they are, then thats likely a problem of its own).

Instead what you find are Entity Attribute Value table designs and the associated administration tool to manage the valid attributes.

Consider the following table:

  +--------------+
  | thing        |
  |--------------|
  | id           |
  | type         |
  | desc         |
  | attr1        |
  | attr2        |
  | attr3        |
  | attr4        |
  | attr5        |
  +--------------+

This is after you've added a few attributes. Instead of attr1 pretend it reads artist or tracks or genre or whatever attributes the thing has. And instead of 5, what if it was 50. Clearly that is unmanageable. It also requires an update of the model and redeployment of the application to handle a new field. Not ideal.

Now consider the following table structure:

  +--------------+     +---------------+    +-------------+
  | thing        |     | thing_attr    |    | attr        |
  |--------------|     |---------------|    |-------------|
  | id           |<---+|  thing_id (fk)|  +>| id          |
  | type         |     |  attr_id  (fk)|+-+ | name        |
  | desc         |     |  value        |    |             |
  +--------------+     +---------------+    +-------------+

You've got your thing with its basic fields. You have two more tables. One with the attributes. Each field is a row in the attr table. And then there is the thing_attr with a pair of foreign keys relating back to the thing table and the attr table. And this then has a value field where you store whatever the value of the field for that entity would be.

And now you've got a structure where the attr table can be updated at runtime and new fields can be added (or removed) on the fly without significant impact to the overall application.

The queries are a little bit more complex and validation becomes more complex too (either funky stored procedures or all client side). Its a trade off in design.

Consider also the situation where some day you need to do a migration and you come back to the application to find that there are now a half dozen or so more attributes than the schema you originally distributed. This makes for ugly migrations and upgrades where the Entity Attribute Value table, when used correctly, can be cleaner. (Not always, but can be.)

Are there any downsides to just modifying the schema at runtime? If the user thinks a thing needs a new attribute, just dynamically add a column to the table?

If you are working with the appropriate flavor of nosql database, you could probably do this (note that the appropriate flavor of the nosql for this would probably be a key-value store which is, well, the EAV table for relational ones described above) without too much trouble. However it comes with all the compromises for nosql which are described elsewhere in great detail.

If you are instead working on a relational database - you need to have the schema. Adding the column dynamically means some subset of the following things are true:

• You are doing meta-database programming. Instead of being able to cleanly map this column to that field with a nice ORM, you are probably doing things like select * and then doing some complex code to find out what the data actually is (see Java's ResultSetMetaData) and then storing that in a map (or some other datatype - but not nice fields in the code). This then throws away a fair bit of type and typo safety that you have with the traditional approach.
• You've likely abandoned the ORM. This means you're writing raw sql for all the code instead of letting the system do the work for you.
• You've given up on doing clean upgrades. What happens when the customer adds a field with one name that your next version also uses? In the matchmaking site the upgrade that wants to add a hasdate field for storing a timestamp has already been defined as hasdate with a boolean for a successful match... and your upgrade breaks.
• You're trusting that the customer doesn't break the system by using some reserved word that breaks your queries too... somewhere.
• You've bound yourself to one database brand. The DDL of different databases is different. Database types are the easiest example of this. varchar2 vs text and the like. Your code to add the column would work on MySQL but not Postgres or Oracle or SQL Server.
• Do you trust the customer to actually add the data well? Sure, the EAV is far from ideal but now you've got some horrendous obscure table names that you the developer didn't add, with the wrong type of index (if any), with no constraints added in the code where there need to be and so on.
• You've given schema modification privileges to the user running the application. Little Bobby Drop Tables isn't possible when you're restricted to SQL rather than DDL (sure you can do a delete * from students instead, but you can't really mess up the database in bad ways). The number of things that can go wrong with schema access either from an accident or malicious activity skyrockets.

This really boils down to "don't do it." If you really want this, go with a known pattern of the EAV table structure or a database that is entirely dedicated to this structure. Don't let people create arbitrary fields in a table. The headaches just aren't worth it.