Phil Factor (5/8/2009)
Denormalisation (you'll live to regret it when the application expands)
sasha.khomich
Don't agree fully. Denormalization is very usefull sometimes. For example, I have ~100 tables which is representing some entities. Few entity types are primary and there are 5 complex searches with ~50 criterias. We decided to create denormalized form of data to provide search. Note, we kept an old structure as primary but populate denormalized data periodically (denormalization takes ~4-5m per day). Search is working very fast...
Ah. Actually, there are several techniques to get around this sort of problem with searches. I'm not sure that you are really denormalising as I understand the term, but using a type of inversion 'index' table. This is a classic approach (Gary Kildall developed it for searching CDs!). It is usually called the ‘Inverted’ or ‘Inversion’ index technique. (see http://en.wikipedia.org/wiki/Search_engine_indexing#Inverted_indices ). I've had an article sitting in a corner I wrote a while back awaiting a bit of a tidy-up. Maybe I'll dust it out....
Best wishes,
Phil Factor