Re:Maybe Normalizing Isn't Normal
by ars on Tuesday July 15, 2008 @06:08AM
(#246534)
That 6 join query is wrong, and the rest of the article follows from that incorrect join.
Why would you ever want a query that returned the user data multiple times, once for every combination of screen_name and phone_number?!? No real site would ever want to do that.
If you need phone number, you get phone numbers, if you need screen names, you get those - but why would you need every possible combination of phone number and screen_name?
He's simply wrong. Speeding up joins is never a reason to denormalize for the simple reason that it doesn't!
A valid reason to denormalize is to precalculate data, which he touches on very briefly. But you always write that twice: once normalized, and that's the primary data, and then again, the "cached/precalculated" version. You should always be able to regenerate that from the normalized data.
OK, one final reason to denormalize which he didn't even write: if you need to do a where clause from one table, but the order by from a different table, you need to denormalize because you can not create a combined index from both tables. (Databases with function indexes might be able to but that's pretty complicated.)
Why would you ever want a query that returned the user data multiple times, once for every combination of screen_name and phone_number?!? No real site would ever want to do that.
If you need phone number, you get phone numbers, if you need screen names, you get those - but why would you need every possible combination of phone number and screen_name?
He's simply wrong. Speeding up joins is never a reason to denormalize for the simple reason that it doesn't!
A valid reason to denormalize is to precalculate data, which he touches on very briefly. But you always write that twice: once normalized, and that's the primary data, and then again, the "cached/precalculated" version. You should always be able to regenerate that from the normalized data.
OK, one final reason to denormalize which he didn't even write: if you need to do a where clause from one table, but the order by from a different table, you need to denormalize because you can not create a combined index from both tables. (Databases with function indexes might be able to but that's pretty complicated.)