Loading

Custom Table vs. Module

Recently I attended Kentico South group micro summit. During one of the presentation interesting discussion has appeared: which one is it better to use - Custom Table or Module class. There were different opinions as well as arguments for or against each of those approaches.
After Kentico introduced custom Modules in version 8, I liked this feature and use it a lot. However Kentico did not removed Custom Tables and, I believe, there was some good reason behind this. So I decided to investigate that reason and give you "apple to apple" comparison.


Features

So let's start from features available within each of our guinea pigs. Following tables shows how different they are from that point of view 

 
Custom Table
Module Class
Fields
V
V + references to other tables
Layout
V
V
Transformations
V
 
Queries
V
V
Alternative forms
V
V
Search fields
V
V
UI
V
requires extra step, but gives you more options
Code
Info class
Info and InfoProvider classes
Versions
V
 
Permissions
Predefined set of permissions
could be added manually


I'm not going to go through the features, those are equal, only through different.

  • Fields: both allow us to add any fields we need, but Module class allows us to reference other tables, which, in fact, means, that this is not standalone table, but a part of some structure.
  • Transformations: Module class does not provide us with the possibility to create transformation, but I'm not really sure if this is a big deal, as we can always go to Page (Document) Types and implements needed transformation there.
  • UI: Custom Tables provide us with Data tab, where we can see all table data with possibility to add or edit table records. Module does not have that feature, but they give us a possibility to implement pages for presentation data the way we need. It is really easy to implement UI similar to the one, Custom Tables provide us with, as there are already prebuild templates for this. But Modules gives us more flexibility, as we can deсide if we want to include, for example, create view, or if we want to show all the record in list view, etc. Also we implement conversion of IDs to object code name, for example, when we have reference to a user, showing his ID in list is not very useful, meanwhile username is much more informative. Module does not limit with this, but allows you to build custom templates where any custom user control could be included, which means we are unlimited there.
  • Code: Kentico generates Info and InfoProvider classes for custom Module class, which makes it much more convenient to work with that object in source code. This also gives us a good way for adding any custom logic to the object processing. Starting from version 8 Kentico allows us to generate a class for a Custom Table which is very similar to Info class. There is no InfoProvider class generated, but, to me, it could be easily implemented in case of necessary. Also Custom Table does not require generating of code behind class for Custom Table, meanwhile it is necessary for a Module class. It is worth mentioning, that adding new code file to Web Application project requires rebuild.
  • Versions: Custom Tables allow object history tracking, not actual table data. Code source control does the same for Module class code files.
  • Permissions: Custom Table has predefined set of permissions for CRUD operations vs. Module requires adding of Read and Modify permissions manually, but allows us to add any permission we need.


Performance

Performance is another good great thing to compare. To be as much accurate with the results as possible, I've created two, exactly the same, data structures and populated with the exact same data. Each table contains columns of different type:

  • Name: Text 200
  • Description: Long text
  • Qty: int
  • Amount: decimal
  • Availability: boolean
  • Date: Date & time

Not too many columns, but, I guess, enough for this test.

I added 135168 records in each table and tried to select all of them, update one and delete one and here is what I got:

 
Module
Custom Table*
Custom Table
SELECT ALL
2.3241329s
2.9881709S
3.0141724S
UPDATE
0.0190011S
0.0230013S
-
DELETE
0.0590034S
0.0600034S
-


*With Custom Table there are two methods to get data:

var records = CustomTableItemProvider . GetItems< ProductItem >() . ToList(); - returns ObjectQuery < ProductItem >
vs.
var records = CustomTableItemProvider . GetItems(customTableClassName) .ToList(); - returns ObjectQuery < CustomTableItem>

According to test results GetItems< ProductItem >() is some faster.

So Modules API seems to be a bit faster and might be considered with huge amount of data. I wouldn't say there is significant difference if we are talking about couple of thousands of records.


Conclusion

In my opinion, those two are great feature and there are cases when we should consider only one of them.

If I need to store and show tabular data, e.g. store hours, job titles, etc., I'd definitely go with Custom Table as it give us all we need out of the box: interface for an editor and a couple of web parts for showing data on the front end. In this case I don't even need to open Visual Studio. So, in general, this is simple and fast approach.

As we can see in the Features paragraph, Modules, usually, requires more efforts, meanwhile provides flexibility, allowing implementation of more advanced scenarios. I'd definitely consider this one when working with data structures consisting from many tables and having some relations in between them.

I hope this post will help you to make right decision and design application appropriately.

Please feel free to leave your feedback or share your experience with similar problems - we highly appreciate this!