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:
Which is represented as the following physical data model:
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
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.
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
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.
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
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).
It's not denormalization, it's amputation.
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...
Why not simplify the model by applying CQRS?
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.
@Alexey Chabunin : It looks like the visual studio class designer.
Comment preview