Options for handling user-generated content

Question

This is pretty much a duplicate from Alex's question on stack overflow, but I was wondering if there had been any developments in the 6 years since that question was asked...

We have a need to store user-generated content - images and files. One approach was documented by Kevin Brechbühl here based on a presentation from Martina. However, on the surface, this feels hacky. Full disclosure, I have not actually tried this yet.

Another approach would be to simply store user-generated content in a separate database.

How are others handling user-generated content?

Answer 1

Things to keep in mind

Whenever you have a site with user-generated content, you need to make sure that you keep the following in mind:

1. Security is both more important and more difficult
2. If storing user-generated media, performance can be a major factor, depending on the media that you are storing, how you store it, and how media-intensive the site is
3. If storing complex objects then custom Sitecore User Profiles are not for you, since all properties must be serializable and you really don't want to slow this down
4. In a scaled environment, you have no access to the Master database (I know that you know this, especially given the post that you linked to, but I have to say it)
5. CDNs are your friend, can be used for all sorts of media, and you can easily throw caches in front of them
6. Using an external database does not prevent you from using Sitecore's HTML Caching on renderings that display the UGC (which means that you can cache the UGC with Sitecore)
7. If you need a GUI to view, edit or moderate the UGC then you will need to factor that into deciding whether or not to use a third-party tool, an extra web database or a custom SQL database
8. If your UGC is a user blog, forum, etc. then you're not in the wrong place, but you might benefit from some of the third-party tools out there, like Telligent, that are designed for these relatively consistent data structures
9. If your UGC is multi-lingual then you increase the complexity of a custom SQL database architecture, and may dramatically increase the overall effort required to build the code, database, and/or a user-generated content GUI

When Should I use a Sitecore database for UGC?

I would use a Sitecore database for large quantities of hierarchical UGC. As an example, if your UGC is for something like a user-blog or a forum, provided that a paid tool like Telligent isn't an option, a Sitecore database may be a good candidate. The hierarchical structure and the ability of the content authors to examine and/or moderate content in the additional database would make good use of Sitecore's tools, and would save you a sizable amount of effort.

When shoudn't I use a Sitecore database for UGC?

As a general rule, it is my opinion that if the content isn't going to be hierarchical then you shouldn't try to make it fit into Sitecore.

Remember that Sitecore is an enterprise-level CMS and its databases are designed to hold pretty much any data, so long as the data is structured appropriately. Sitecore is great with caching and all the performance voodoo, but it is still has a lot of overhead. You can create a SQL database to store UGC for a user and throw a cache in front of it and you will not only still be able to use Sitecore's HTML Caching, but your database will likely be a lot smaller, simpler and more performant than a Sitecore database.

How should I store my UGC if I am not using a Sitecore database?

First, CDNs are your friend. Use them for all your media. You can add all sorts of caching between them and your application, and you can store references to them (e.g. image URLs, etc.) in your custom database or User Profiles. I cannot recommend CDNs strongly enough.

Second, Entity Framework and SQL are great if you have a lot of data to store, or if you have to store complex objects. EF is great for performance too, you can use Linq with it OOTB, and the sky is the limit with your data structures so you can make it suit your needs. There are, however, three problems with making your own SQL database to hold your data:

1. It can take time to come up with the right database architecture
2. Making changes to tables later on isn't always as straight-forward as you might think
3. If you need a GUI for your authors to edit, review or moderate the UGC then you will have to create it from scratch

Third, try to store as little data in custom Sitecore User Profile properties as possible. Remember that User Profiles are serialized and deserialized, so loading them up with large amounts of information and heavy objects will have a noticeable impact on performance. I recommend storing only something like the associated user ID from the custom database in the user's profile.

Fourth, if your solution is multi-lingual and you go with a custom database then be sure to make your language fallback consistent, reusable, overridable, simple, and easily controllable. I have seen the treatment of multi-lingual UGC make or break the implementation more often than I have seen anything else do so.

What should my custom database look like?

Your custom database can take many forms, depending on your implementation. In the spirit of re-usability, whenever I have a very large quantity of widely varied UGC to store, I prefer to use a flexible database architecture similar to the following (examples provided):

User (ID, Username)
|- jsmith
|- bjordan

ContentTypes (ID, Name)
|- Text
|- Rich Text
|- Media Reference
|- Numeric
|- Sitecore Reference
|- Sitecore Delimited Reference

ContentFieldDefinitions (ID, Name, ContentType)
|- Address, Text
|- Age, Numeric
|- Profile Photo, Media Reference

ContentFields (ID, DefinitionID, UserID)
|- Address
|- Age
|- Profile Photo

Languages (ID, Name, Code)
|- English, "en"
|- French (France), "fr-fr"
|- Spanish (Mexico), "es-mx"

ContentFieldValues (ID, FieldID, LanguageID)
|- "John Smith"
|- "110 Core Site Ln"
|- "13"
|- "Esto es un ejemplo"
|- "http://cdn.mydomain.com/images/someimage.png"
|- "{B07D2864-0A7A-4AC1-B729-89D44FABBFE9}"
|- "{966FA00C-9AD2-44F9-AB44-0DA65B94FB27}|{075701C7-2558-40B2-AC16-CFE930F9C65C}"

Basically, I use a very loose structure, similar to Sitecore's own (though mine is flat, while Sitecore's is hierarchical), which supports pretty much any type of UGC, regardless of complexity level or language-specific content. This flexible architecture saves me a lot of effort in my implementations and makes the solution pretty easy to extend and maintain, but it costs me in performance and database readability without a GUI.

For those implementations that store a smaller quantity of varied UGC types, I tend to create a more tailored and specific database architecture. This saves me on performance and readability, but costs me in effort, extensibility and maintainability. As effort is often a major factor, I tend to only go this route if performance is a serious concern, or if the data being stored is relatively simple. Note that following this path for multi-lingual content can be a path to the "dark side" if you are not careful. Avoid writing the same multilingual support code, tables and columns over and over again.

Answer 2

It's not clear from your question whether your requirement is for just images and files or content, images and files - I'm going to assume just images and files :)

I think to fully answer this question you'd need to know more about the context. Some important questions you might need to ask yourself:

1. Does is make sense to treat the images and files as part of your Sitecore content in some way?
2. What is the content going to be used for and where?
3. Will uploaded files need to be scanned for viruses?
4. Do you need workflow on the uploaded assets?
5. What sort of load on these assets are you expecting?
6. How big are they going to be?

Without knowing your exact requirements, I would recommend using some sort of CDN for uploading and serving assets rather than content.

If you require something "behind the firewall", you could look at the various DAM options out there.

On one project I've worked on, they had a company CDN and it was a requirement that this was used for storing assets so it may be that there's something you can leverage within the business already.

Also, if virus scanning is a concern, you should investigate getting an isolated server for handling and checking files before they're served up by your website etc.

I think Kevin's approach is sound for content itself but there's a lot more to think about for images and files. If you need both - perhaps a combined approach would be the way to go.