Ayende @ Rahien

It's a girl

Resolution: Efficient Querying

Well, I already covered how you can handle this challenge several times in the past, so I’ll not do that again. What I actually did is quite different. Instead of having to deal with the complexity (which is possible) I decided to remove it entirely.

The solution to my problem is to simplify the model:

image

Which is represented as the following physical data model:

image

Now, querying for this is about as simple as you can get:

select user0_.Id          as Id2_0_,
       book2_.Id          as Id0_1_,
       user0_.Name        as Name2_0_,
       user0_.Street      as Street2_0_,
       user0_.Country     as Country2_0_,
       user0_.City        as City2_0_,
       user0_.ZipCode     as ZipCode2_0_,
       user0_.HouseNumber as HouseNum7_2_0_,
       book2_.Name        as Name0_1_,
       book2_.ImageUrl    as ImageUrl0_1_,
       book2_.Image       as Image0_1_,
       book2_.Author      as Author0_1_,
       queue1_.[User]     as User1_0__,
       queue1_.Book       as Book0__,
       queue1_.[Index]    as Index3_0__
from   Users user0_
       inner join UsersWaitingBooks queue1_
         on user0_.Id = queue1_.[User]
       inner join Books book2_
         on queue1_.Book = book2_.Id
where  user0_.Id = 1 /* @p0 */

De-normalizing the model has significantly improved my ability to work with it.

Comments

Frans Bouma
02/13/2010 12:00 PM by
Frans Bouma

You have an objectified relationship in your model (queue) which by definition is an entity on its own. You see that with the table it requires (UsersWaitingBooks). In general it's a 'smell' (to use a term common to code oriented folks ;)) to hide these entities under the rugs, as they contain non-pk attributes (fields) which means they can't be saved properly.

CodeAddict
02/13/2010 01:59 PM by
CodeAddict

MySQL has a GROUP_CONCAT function for this. I don't know if it performs well, and it's probably not supported in any other RDBMS. But I find it a nice solution to the problem. I use it a lot for selecting all tags on a set of posts in one query.

With GROUP_CONCAT the query could be:

SELECT u., b., q.Index, GROUP_CONCAT(CONCAT(a.Id, ';', a.Name) SEPARATOR ',')

FROM Users u

JOIN UsersWaitingBooks q ON q.User = u.Id

JOIN Books b ON b.Id = q.Book

JOIN BooksAuthors ba ON ba.Book = b.Id

JOIN Authors a ON a.Id = ba.Author

WHERE u.Id = 1

Leonardo
02/13/2010 02:23 PM by
Leonardo

But you changed the model, now you are constraining the books to have only one Author, where as the previous model allowed to have various ones. That is something that is not always applicable, you had to know something else.

CodeAddict
02/13/2010 03:04 PM by
CodeAddict

Ah that's true, I forgot a GROUP BY at the end:

SELECT u., b., q.Index, GROUP_CONCAT(CONCAT(a.Id, ';', a.Name) SEPARATOR ',')

FROM Users u

JOIN UsersWaitingBooks q ON q.User = u.Id

JOIN Books b ON b.Id = q.Book

JOIN BooksAuthors ba ON ba.Book = b.Id

JOIN Authors a ON a.Id = ba.Author

WHERE u.Id = 1

GROUP BY b.Id

Andrey Shchekin
02/13/2010 08:28 PM by
Andrey Shchekin

This is something that requires a specific mindset. Even when I do some hacking/duct-taping, I can not force myself to use denormalization (except for caching/as a rebuildable view).

Rafal
02/14/2010 07:39 AM by
Rafal

It's not denormalization, it's amputation.

Alexey Chabunin
02/15/2010 02:08 AM by
Alexey Chabunin

An off-topic question: What tool do you for class diagram drawing? I saw it in ASP.NET MVC book and now in your blog - it looks clean and clear...

John Simons
02/15/2010 03:02 AM by
John Simons

Why not simplify the model by applying CQRS?

Richard Armstrong
02/15/2010 06:58 AM by
Richard Armstrong

I agree with this approach, though how would one represent multiple authors? As serialzed JSON or XML data? For those that don't like denormalization you should read this article by Obasanjo: Building Scalable Databases. Some applications just won't scale without it.

Josh Schwartzberg
02/15/2010 10:03 PM by
Josh Schwartzberg

@Alexey Chabunin : It looks like the visual studio class designer.

Comments have been closed on this topic.