Many ways to relate Orchard things, part 1: database relations
A long time ago, I wrote a documentation topic on how to implement 1-n and n-n relationships in Orchard using database relations. It was much needed at the time, as this was still a difficult topic. Between spotty mapping mechanisms for relationships, and specific Orchard conventions, it wasn’t something you could expect just anybody to figure out on their own. It is still the way to go under some conditions, but those conditions are arguably very uncommon, and there are now much better solutions for more common cases.
In this series of posts, I’ll show one technique per post, with pros and cons, and use cases for each. Today, we begin with database-bound relationships.
I won’t detail the technicalities for this method, as they are explained in details in the doc topic. Please go and read it if you haven’t already.
Pros
- Can leverage the power of SQL and nHibernate for fast or complex queries.
- Pretty clean from a DBA’s point of view.
- Allows for multiple relationships per type.
Cons
- Requires a fair amount of boilerplate code, every time.
- Doesn’t work well with import/export, unless you’re willing to write more code, and even then will be imperfect.
- Easy to create select n+1 issues.
Use cases
I’m tempted to limit my recommendation to use this technique to cases where no other technique is applicable. As you’ll see by reading the other posts in this series, that leaves very few situations. Specifically, when you need to perform database queries against the related entities (and even that can often be done more efficiently in other ways).
This brings another interesting point into the spotlight: why is Orchard contents modeled using the relational model? The way it composes items from parts linked by 1-1 relationships means that many queries have to join as many tables as there are parts, which drags down performance. Other relationships make it easy to create select n+1 issues, another drag on performance. In comparison, document storage is a much more natural fit, in which most relationships disappear. One drawback is that you have to re-invent querying, by managing and querying indexes, but there is a lot to win, and little to lose in doing that. This is why Orchard has been increasingly using document storage, and has been implementing new, not-only-SQL querying mechanism, such as Projections. This is also why DecentCMS, my Node.js CMS, is 100% document storage, and does its querying through a map/reduce-like API.
If you want to point out something I may have missed in the pros, cons, and use cases, or if you want to share stories of success or failure with this technique, please let me know in the comments below…