Image 01 Image 02

Don’t Be Afraid To Break Database Normalization

Posted on 18th August 2008 by Sameer
1

For those of us who took an intro database class, the topic of database normalization was covered ad homonym as an absolute necessity in any database design. So, it takes a little bit of effort to pull yourself away from such a time tested concept. But, sometimes its absolutely necessary. (For those who are not familiar with the term, database normalization basically means “don’t repeat any information ever”).

Let’s say you have a social network site with a huge table of private messages between users with each row representing an individual message between two users. Each time a user logs in you want to query the number of unread messages to the user with a query like:

select count(*) from privatemessages where unread = 1 and userid = $userid

However, you find that because your private messages table is 6gb’s large and that some users have thousands of messages that the query is taking an unacceptably long time to run. So what do you do?

You could use a cache such as Memcached to store the number of unread messages for each user in memory, which would yield a significant improvement. However, Memcached still needs to occasionally run the query. To speed up the query, you break database normalization. Why not create another table called privatemessagescount which could store the number of unread messages per user (with columns “userid” and “numunread”). Each time the user receives a new message or reads a new message there will be an additional write to the DB, but it will be a simple write. And more importantly, your reads will be much faster because you will be looking up on a primary key (“userid”). Now the query simply becomes

select numunread from privatemessagescount where userid = $userid

So for performance sakes you’ve broken database normalization and added additional overhead to your application. But, I’d rather have a fast site than a normalized site that is too slow to be used.

lig tv

ligtv

maç izle

canlı maç

canlı izle

футбол онлайн

трансляция футбол

смотреть онлайн футбол

смотреть футбол

soccer live

soccer tv

live soccer streaming

stream soccer

online football

watch football

football match

football streaming

live streaming

watch football

live football

football tv

futbol vivo

partido en vivo

juegos futbol

futbol online

futbol gratis

roja directa

jogos de futebol

jogo de futebol

futebol online

assistir tv

atdhe

foot en direct

jeux de foot

jeux football

calcio diretta

calcio streaming

giochi calcio

live ποδόσφαιρο

podosfairo live

αγωνεσ ποδοσφαιρου

video sepak bola

game bola sepak

Siaran Langsung Sepakbola

bola siaran langsung

futbol juegos

partidos en vivo

bóng đá online

xem bóng đá

bóng đá trực tuyến

bóng đá trực tiếp

ฟุตบอล online

ฟุตบอลสด

บอล online

ดู ฟุตบอล สด

ถ่ายทอด สด

online futball

live fussball

fussball live stream

live stream fußball

bundesliga live stream

fußball live

bundesliga fußball

piłka nożna na żywo

piłka nożna online

fotbal live

fotbal online

fotbal živě

fotbal zive

fotbollskanalen

fotball live

fotball på tv

футбол онлайн

футбол трансляція

футбол канал

live voetbal

live voetbal

voetbal kijken

voetbal online

фудбал уживо

fudbal uzivo

live fudbal

futbal online

live futbal

live footy

مباريات كرة القدم

مباشر كرة القدم

بث حي مباشر

مباشر مباريات

כדורגל שידור ישיר

שידור חי כדורגל

بث كرة القدم

football forum

ライブサッカー

足球实况

足球

축구생중계



1
Response to.. Don’t Be Afraid To Break Database Normalization

1

[...] the database is laid out in a highly normalized form which is not ideal for performance, as it requires joins to be performed over multiple tables. A quick look at the db profiler shows [...]



Leave a reply...