This or That: Many Item Relationships

In any Site or Entity, there are Many-to-One or Many-to-Many relationships.  Whether it be something as simple as “Multiple Banners to display on this page” to the more complex relationships that are needed to do custom ups (such as "Multiple states this dealer serves").  The tricky part is, there are multiple ways to h­andle multiple relationships in Kentico, each with pros and cons.  By the end of this article however, you should be able to know which is the best thing for the situation!

Option 1: Tree Structure

The Content Tree is a great place to store structured content.  Since the Parent-Child relationship exists in a tree, this is a great place to start when dealing with Relationships.  Any child object can be easily referenced using the NodeAliasPath (in things such as repeaters, you can use relative path expressiones to select any child element) and the Page Type can easily help you maintain multiple types of children.  You can even specify through Page Scopes where you can insert certain items, and through Allowed Parent/Child Page Types, what elements you can add below.  This also allows for easy ordering of display items.

Another plus side is if the items may need to be localized in the future, such as banners with text, the Tree Nodes are already integrated in with this.  If you need to share a child across multiple parents, you can also use the Linked Document to have 1 “Source” spread across multiple places.

That being said, there are some down sides.  First is if you have many “Parents” using the same “Children” then doing linked documents can become a nightmare, and although you can quickly create linked documents, if there's more than 5-10 then this can be slow processes.  Also for advanced lookups, doing a text “Like” on the node alias path isn’t the fastest if your data becomes large.  Thirdly, if your parent object has many types of children, the tree structure can get pretty messy.

Verdict:

Difficulty: Novice
Pros: Easy to use, can share children, localization, easy lookup
Cons: Not good for large sets of relationships, not fastest in lookups, can bloat content tree.
Great For: Banners or other page elements that need a repeater to display.

Option 2: Character Delimited List

Another option is to use a Character Delimited list, such as “option1;option2;option4” in a long text field.  This is an easy to store a multiple relationship, as all you need to do is use the Multiple Choice form control on the field, or even better the Uni Selector form control.  Once you have your values in, you can use this in queries through some text manipulation, such as a WHERE condition of:

However you must be very careful to make sure your options either are scrubbed for single quotes and also there isn’t a way for malicious SQL to be injected in. 

There also is a nifty SQL Command I’ve used in the past, you can use the below Stored proc to select from a delimited list (had to make a separate one for a GUID) that allows you to leverage the fields in a where condition with a statement like:

One major pitfall to this method however is that the system only knows your value as a text block.  If you delete an option, it won’t know enough to remove that from the delimeted list in your field, so you can easily end up with references to things that no longer exist.  Also, you want to limit it to CodeNames and GUIDs, NEVER reference IDs in a delimited list because IDs can change if you have multiple environments, and my previous article highlighted the dangers of breaking Kentico with bad coding practices such as this.

Verdict

Difficulty: Beginner
Pros: Can be very quick to set up, and can leverage Uni Selector on almost any object
Cons: Doesn’t track references if something is deleted, can require some tricks to leverage / not easy to use in SQL, and no ordering.
Great For: Quick references that can be many items long, that don’t require looking up frequently.
 

Option 3: Category / Document Tags

A built in option that also exists is the Kentico Categories, and Document Tags.  Both of these (by default) can only be applied to Pages on the content tree.  Categories allow you to easily define taxonomy for your objects, and while the default Kentico Category Selector form control isn't the greatest, the Advanced Category Selector I created makes up for that.

Categories are stored in a Category-to-Document table (CMS_DocumentCategory), so these things are lightning fast for lookups because it’s doing int-based lookup on the Primary Keys.  The where condition would go something like

You can also easily see what other pages are in certain categories through Kentico’s Category interface. 

Document tags also operate similarly, as you can tag a Document and Select existing tags.  These are a little more free-flowing, and are good for simple, non-structured relationships.

The potential downside to these however is they are linked on the Document, and not the Node.  If your relationship / tagging needs to be on the Document (that Language version of the Node), that’s perfect.  However, if you want the relationship to exist across any language version of a page, then these will not work as is, as each Language version of a document will have its own Tags/Categories.

Verdict

Difficulty: Beginner
Pros: Easy to use, built in Kentico functionality, for Categories can be super-fast lookups and easy to select multiple categories with the Advanced Category Selector.  Categories can be Localized, Document tags not.
Cons: Linked to Document instead of Node, so may pose problem if Localized.  No ordering.
Great For (Categories): Grouping Content and Defining Taxonomy
Great For (Document Tags): Tag Clouds, simple free flowing organization.

Option 4: Related Pages

Another way Kentico has built in relationships is through it’s Related Pages system.  Related Pages relate one Node with Another, and since it’s on the Node it has the benefit of sharing relationships across Localized documents.  If you are using Repeaters, the Repeater also has the configuration to select these relationships and display them without needing to do anything more than that.  They also allow ordering, so you can order the relationships, unlike Categories, Tags, and Delimited lists.

The downside to the Related Pages is if you use the "Pages" form control so the relationships are managed on the form, it’s kind of hard to reference them outside of using a Repeater.  The Relationship Name is automatically generated based on the Page Type code name plus the Related Page’s Field’s GUID, you can’t just set it.  This bugged me so much I actually built a custom Related Pages Form control just so I could set the Relationship Name manually to something that made more sense (If you want it, just contact me).  If you don't do this, then all the Related Pages have to be set through the Related Pages UI (under the General tab of Pages), and the user needs to select the proper relationship when creating it.

One other con is it can take a while to select multiple pages, as you have to select each item from the Content Tree individually.  There also isn't a way to "limit" what types of pages you want to relate, so the user needs to be careful to only relate the right type of pages.  But other than that, it is a great option and can be thought of kind of like the Children on the Node Tree, except no need for linked documents to share.

Verdict

Difficulty: Beginner if using Repeaters, Intermediate if trying to call in code/where condition
Pros: Relationship on the Node so not affected by Language, no need for linked documents, ordering built in.
Cons:  Can take a while to select large quantities, default Relationship Name is hard to decipher if not using a repeater, no ability to control what types of pages are relatable for a given relationship.
Great for: Relationships to other Pages between 0-20 items.
 

Option 5: Custom Binding Table

The last option really is the best way to handle most relationships, however it is the one that requires the greatest skill to use.  Using Kentico’s Modules, you can create your own Binding Classes, that reference one entity and another.  If you set the Type Info method up properly, it can have full staging support while maintaining the speed of integer ID lookups.  And with my Advanced Many-To-Many Selector (And Advanced Category Selector’s Join Table Support), these relationships can be easily populated. 

Doing SQL queries is pretty easy as you can just do a couple “in” queries or joins to gather the relationships, and since they are on the IDs it can be pretty instantaneous. I use this approach a lot, especially in combination with Kentico’s Categories (using a Custom Binding Table that references the NodeID and the CategoryID).  If you feel comfortable setting up Custom Modules, generating and modifying the Type Info, and modifying / leveraging the Advanced Many-To-Many Selector / Advanced Category Selector, this is solid.  The only thing this doesn’t do well with is ordering, so keep that in mind.

Verdict

Difficulty: Advanced
Pros: Super-Fast and easy lookups, no issues with staging when properly set up, easy to select many items through the tools provided, you can control how the relationship looks.
Cons: Ordering not supported by default, requires setup of custom binding classes and modules.
Great For: Entities with multiple children, that require fast lookups and easy entry.
 

BAD IDEA – Just add more Fields!

I thought I would also point out a couple bad ideas when it comes to relationships that I’ve seen, one being the dreaded "just add more fields" approach.

What I have seen, much to my shock, is some people have added ‘multiple’ relationships by simply adding multiple fields onto a form.  For example, they have a Home Page page type, and they have a Banner_1, Banner_2, Banner_3 field to allow the user to have 'multiple banners.'

But let’s think about this…first thing is, what if they want 4 banners?  Now you have to add another field, bad.

Next, if they want to change the ordering of two of the banners.  Well with these fields, sorry, you have to copy and paste around to move the ‘banners’ into other positions.

Not only that, but your Form tab where you enter this is can get gigantic. Not to mention the horrible database bloat this can cause, and you can’t easily localize the content either. 

Don’t do this, ever.  Be a better programmer.

Conclusion

I hope that this helps you now decide which way to go.  With Kentico, there are always multiple ways to do anything, and picking the wrong way can lead to a disaster later.  Ask the questions, inform the client on the pros and cons, and you’ll look like a Kentico hero in their eyes, and whoever must handle the project after you are gone!