http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660@Alexey Chabunin : It looks like the visual studio class designer. http://ayende.com/4398/resolution-efficient-querying#comment10http://ayende.com/4398/resolution-efficient-querying#comment10Mon, 15 Feb 2010 22:03:02 GMTI 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. http://ayende.com/4398/resolution-efficient-querying#comment9http://ayende.com/4398/resolution-efficient-querying#comment9Mon, 15 Feb 2010 06:58:24 GMTWhy not simplify the model by applying CQRS? http://ayende.com/4398/resolution-efficient-querying#comment8http://ayende.com/4398/resolution-efficient-querying#comment8Mon, 15 Feb 2010 03:02:34 GMTAn 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... http://ayende.com/4398/resolution-efficient-querying#comment7http://ayende.com/4398/resolution-efficient-querying#comment7Mon, 15 Feb 2010 02:08:11 GMTIt's not denormalization, it's amputation. http://ayende.com/4398/resolution-efficient-querying#comment6http://ayende.com/4398/resolution-efficient-querying#comment6Sun, 14 Feb 2010 07:39:18 GMTThis 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). http://ayende.com/4398/resolution-efficient-querying#comment5http://ayende.com/4398/resolution-efficient-querying#comment5Sat, 13 Feb 2010 20:28:57 GMTAh 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 http://ayende.com/4398/resolution-efficient-querying#comment4http://ayende.com/4398/resolution-efficient-querying#comment4Sat, 13 Feb 2010 15:04:23 GMTBut 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. http://ayende.com/4398/resolution-efficient-querying#comment3http://ayende.com/4398/resolution-efficient-querying#comment3Sat, 13 Feb 2010 14:23:39 GMTMySQL 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 http://ayende.com/4398/resolution-efficient-querying#comment2http://ayende.com/4398/resolution-efficient-querying#comment2Sat, 13 Feb 2010 13:59:25 GMTYou 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. http://ayende.com/4398/resolution-efficient-querying#comment1http://ayende.com/4398/resolution-efficient-querying#comment1Sat, 13 Feb 2010 12:00:27 GMT