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:
- Security is both more important and more difficult
- 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
- 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
- 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)
- CDNs are your friend, can be used for all sorts of media, and you can easily throw caches in front of them
- 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)
- 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
- 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
- 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:
- It can take time to come up with the right database architecture
- Making changes to tables later on isn't always as straight-forward as you might think
- 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)
ContentTypes (ID, Name)
|- Rich Text
|- Media Reference
|- Sitecore Reference
|- Sitecore Delimited Reference
ContentFieldDefinitions (ID, Name, ContentType)
|- Address, Text
|- Age, Numeric
|- Profile Photo, Media Reference
ContentFields (ID, DefinitionID, UserID)
|- 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"
|- "Esto es un ejemplo"
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.