EXPLAIN EXTENDED

How to create fast database queries

Archive for August, 2013

Did You Know You Could Index SUM() and COUNT(): SQL Server Indexed Views, Part 3

Comments enabled. I *really* need your comment

A friend of mine who owns a stock photo website once told me he had designed an awesome photo auto-tagging algorithm which was 80% correct: just tag all photos cats and have four hits out of five.

Cat

But seriously, how do we calculate most used tags on photos?

For those not familiar with different tagging models, I highly recommend this old but very useful article: Tags: Database schemas.

Now, in this tutorial, we'll use the toxi approach to tagging. Later in the article I'll explain why we would choose that approach over the others.

Let us create a simple schema which would allow us to store, tag and sell photos:

Read the rest of this entry on tech.pro »

Written by Quassnoi

August 26th, 2013 at 11:00 pm

Posted in SQL Server

Storing a Friendship: SQL Server Indexed Views, Part 2

with one comment

You're building a social network and want to store who is friends with whom. OK. Let's assume you want Facebook-style friends: by agreeing to be friends with someone you allow them to be friends with you too. This is called a symmetric relationship and if your social model sticks to that kind of relationship, your application should enforce its symmetry.

How shall we store friends in a database table? There are several options.

Initiator first

A table with one record per friendship and two fields for each of the friends. The person who requested the friendship is stored in the first field:

Read the rest of this entry on tech.pro »

Written by Quassnoi

August 14th, 2013 at 11:00 pm

Posted in SQL Server