Kentico Document Types vs. Custom Tables

Posted: 26/09/2011 11:38:53 by Jirka Francek

I was thinking about writing an article that has some e-learning effect and that would help others to build or propose good web sites built on Kentico CMS. I think almost everybody who has worked with Kentico for a few months knows the terms Document type and Custom table. Both are capable of storing data, which can be displayed on the pages via webparts like repeater or datalist. But what are the real differencies and where should be each type used? Are there any performance issues if you’re considering data sets of 10.000+ records? How does the design fit in your database model and what functional requirements do you have? I’ll try to answer these questions and write a comparison side by side, so you can decide which solution fits your needs the best.

Comparing Document Type and Custom Table

Kentico Document Type Properties
Screenshot 1: Document Type Properties

Kentico Custom Table Results View
Screenshot 2: Custom Table Results View

Document types are designed to be primarily used as Kentico Menu Items. They are always part of CMS tree. You can translate all their data into another languages, you can advance them with workflow and versioning and more.

On the other hand Custom tables are never part of CMS tree, you can’t translate their data, nor configure a workflow or versioning – custom tables are just database tables.

Let‘s take a look at comparison of all possible features side by side.

Feature Document type Custom table
Can hold any convencional data types
(int, float, varchar, ntext, datetime)		 Yes Yes
Can hold binary stream data (files) Yes No
Can hold one-to-many data (drop down, radio buttons) Yes Yes
Can hold many-to-many data (check box list) Yes Yes
Data can be transformed into any layout
(using ASPX or XSLT transformation)		 Yes Yes
Can be used as E-commerce product Yes No
Data can be populated/edited by CMS Editors Yes No by default
(can be overridden)
Can be used in 'Listings' webparts
(repeater, datalist, grid)		 Yes Yes
Can be used in 'Navigation' webparts Yes No
Can be used in 'User contributions (Wiki)' webparts Yes No
Workflow Yes No
Versioning Yes No
Multilingual content Yes No
Accessible from CMS Tree Yes No
Each record has its own URL Yes No
Number of database tables that store the data 3 - 6 1
Database View is automatically created Yes No
API classes to access the data and properties Yes Yes
Recommended for large number of records (10.000+) No Yes
Importable / exportable Yes
(can be difficult)		 Yes
(usually easily)

As you design your site, you can simply ask questions which features you need from the table above and decide which solution would fit the best.

As you can see, there are number of differences and only a few common features. The common features are actually emerging from CMS Site Manager’s design where you can find almost identical tabs for both document types and custom tables. On the contrary the doc.types and CT’s are internally completely different. While the doc. types are stored at least in 3 database tables (CMS_Tree, CMS_Document, Coupled table – for details please read http://devnet.kentico.com/docs/5_5r2/devguide/index.html?content_management_internals.htm) every single record is stored in all of these tables at least once (multiple times in CMS_Document and Coupled table when the content is multilingual).

Kentico Document Type Database Structure
Screenshot 3: Database Structure of a Document Type

Performance

The Custom Tables perform as native SQL tables. The Document types perform generally well but all depends on your data model. If you have a complicated data model where there is a lot of tables with a lot of data and maybe complicated queries to list the results, you can expect noticable performance degradation when querying the data from the database. This is because the data is actually stored in CMS_Document and CMS_Tree tables so there may be a big amount of data for all the tables. Second, if you access the data via the standard webparts like Repeater, Datalist, etc. (they use default views generated by Kentico engine) these views and webparts can consume a lot of power and memory that may not be necessary for your solution. I.e. they always compute Published status from various tables and properties, join many other tables, but you only need a few results to be displayed on the page. Therefore Repeater with custom query and similar webparts should help to perform better.

Indexing Tables

When speaking about improving performance for large datasets or complex SQL queries, it is generally best practice to add SQL indexes on the tables where appropriate. You have to do this manually through T-SQL statements or use a tool like SQL Management Studio.

Converting Custom Tables to Document types

It is generally not a big problem to convert a single custom table (even with data) into a document type. If you have heavily used that custom table on your site (i.e. many webparts link to it) it might be important for you to preserve the name of the table. You can follow these steps to do so:

  1. Take a backup of your Kentico database just in case!
  2. Rename the custom table code name in CMS Site Manager (i.e. to „MyCustomTable_1“ where the „_1“ means the change).
  3. Rename the custom table Table name. To do so, you need to go directly to your SQL database and
    1. locate the custom table row in CMS_Class table and edit it’s
      1. ClassTableName to „MyCustomTable_1“;
      2. ClassXmlSchema – here replace the old table name with the new one;
    2. change the name of the table „MyCustomTable“ to „MyCustomTable_1“.
  4. Create the target document type and setup all the fields.
  5. Setup the Kentico SQL Import utility. It is usually located in 'c:\Program Files (x86)\KenticoCMS\5.5R2\SQLImport\SQLImport.exe.config ' if you installed Kentico in it’s default location. Now copy the ConnectionString of your site’s web.config into the SQLImport.exe.config to both SourceConnectionString and CMSConnectionString. You can learn more about the Kentico Import Toolkit here: http://devnet.kentico.com/Blogs/Martin-Hejtmanek/July-2010/Kentico-CMS-5-5-Import-Toolkit.aspx.
  6. Start the SQLImport.exe and follow the instructions on screen.
  7. Now you can use the new document type containing data from your old custom table.

Converting Document Types to Custom tables

If you find out that there is no need to store your data as a document type you may wish to go the opposite way and convert the doc.type into a custom table. Here are the steps:

  1. Take a backup of your Kentico database just in case!
  2. Create new custom table and setup all the fields.
  3. Open your SQL management studio and type the SQL INSERT statement to bulk insert the data from the document type’s coupled table into the new one. You can use following example:
    Kentico SQL Code: Converting Document Types to Custom tables
  4. Now you should see the data in CMSDesk -> Tools -> Custom tables -> new custom table.