Database Design – Naming a Table Storing Only Foreign Keys

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

I usually go with the multiple table approach.

You might find however, that in terms of read/write performance your current single table format outperforms both alternatives.

XML columns for structured data in a relational database are, I think, trying to do the same job as tables and columns. Before you head down that route, consider the problems you may encounter when in the future you need to change the format of the data.