Brett,

I think that I'm starting to understand your point about denormalisation. I've written a number of custom queries myself so  I am aware of the join issue. As one of the major points of Elgg is to avoid writing SQL where ever possible, I think that ease of coding SQL is not much of an issue (if I need to write a few joins for the occasional custom query, I can manage that!)

Performance is by far the most important issue about denormalisation and I would like to understand this more.

I believe that the indirection through the metastrings table is there to map identical strings to a single integer value and was intended to speed up tag searches.

Have you concluded that this approach ended up being more trouble than it was worth and that embedding strings directly in the metadata and annotation tables would be more efficient?

I'm convinced that denormalizing these tables will increase speed and scalability of Elgg overall, but will do bench marking to confirm this.  If it turns out denormalizing doesn't help, it won't be changed.

Normalizing a DB structure is almost always a best practice.  I read an article once that essentially said "Normalize until it hurts, then denormalize for performance."  I think we've done the first part well, but would like to get into the second part.  With some big players switching to non-relational NoSQL approaches (Google's Big DB, Facebook's Cassandra, Amazon's Dynamo), normalized relationship databases are starting to be viewed as outmoded by some.  I don't fully buy into that (yet...) but do see the benefits of denormalizing in certain situations, and I think Elgg's metadata is one.

I'm quite interested in this specific issue (OK call me a database nerd) so when I get a few spare moments, I may try some string table performance testing myself.

I've been looking at whether adding a hash value to the string table might speed things up as well.

Tag searches are not as important as they used to be now that there is full text search, but Elgg still needs to look up metadata names, so I am very curious about the different approaches.

Something in the back of my mind also suggests that reducing an Elgg object to a list of integers (as the current system does) might help in building an object cache in the future.

Cassandra is coming... ;)