More in this section

Forums / Developing with Sitefinity / Another upgrade fiasco - and please normalize your tables!

Another upgrade fiasco - and please normalize your tables!

7 posts, 0 answered
  1. fregas baratis
    fregas baratis avatar
    185 posts
    Registered:
    14 Nov 2002
    13 Dec 2008
    Link to this post
    We did a 3.x to 3.5 upgrade this week on one of our existing websites, and ran into some pain.  On of our other developers spent 4 hours working on it, which in fairness is partly because we have our own customizations in the site.  However, he got stuck on one particular error.  The error could have easily been avoided if you would just NORMALIZE YOUR DATABASE.  Its really not that hard and would help us developers greatly.  I'm not sure if its your developers that don't normalize certain things or if its Nolics itself, but not normalizing means more room for these types of errors and more data that has to be touched when somethings changed.  Below are the steps I as the lead developer had to go thru to debug and correct this problem:

    So the other developer was getting this error related to the Sitefinity upgrade that said “Key not found for value UpcommingEvent.” 

    "Upcomming" was misspelled so i figured the error was related to the Sitefinity developers correcting the spelling.

    Looking through the stack trace, I saw it was trying to parse this value from into an enum and not finding that string in the enum thus throwing the error.

    Since all the code had been completely replaced by the new version (3.5) I assumed the problem had to be in the database.

    There are like 100+ something tables in Sitefinity, so going thru them one by one was out of the question.

    I opened up sql profiler, ran the page again and collected all the generated queries to the database from sitefinity.

    Nothing in the queries mentioned “UpcommingEvent” so instead I ran all the queries that profiler found and looked at the results.

    I converted the query results from grid view to text view in sql management studio.

    I then did a find for UpcommingEvents, and found the text!

    I found the query that got that result to find the table and column holding that event, something like "sf_cmscontrolproperty" and "value".

    I did a LIKE search on that table and column to find all the records that had “UpcommingEvents”.  19 records had this text in it!

    Not knowing what the right value was to put in there, I did a search in vs.net in object browser for anything that had “Upcoming” (spelled correctly) in the hopes I would find an enum with this value and that this would be the correct value.

    Sure enough there WAS an enum and it had 3 values, something like PastEvents, CurrentEvents, UpcomingEvents (spelled correctly.)

    I copied the exact enum value into sql query analyzer and updated the 19 records from “UpcommingEvents” to “UpcomingEvents”

    I ran the site again, and it was fixed.

    This took over an hour.


    If the database had been normalized, with just the value of the enum (the integer) in the database, this could have been avoided.  You could have changed the names of the enums without hurting the data at all, as long as you didn't change the underlying values.

    Anytime you see repeating data (in this case "UpcommingEvents" your breaking normalization and creating work for yourselves and/or your customers.  3NF has been around for a long time, I'm not sure why so some people choose not to use it when it prevents a slew of problems.  Its okay to denormalize certain data thats non-authoratative and can be recreated from normalized data (such as for reporting) but this wasn't the case. 

    Thanks,
    Craig

  2. Gabe Sumner
    Gabe Sumner avatar
    440 posts
    Registered:
    09 Sep 2007
    13 Dec 2008
    Link to this post
    Hey Craig,

    I noticed your blog article on this subject at DotNetTricks.com.  I posted a comment on your web site.  I'm sure the team will weigh in on this subject as well within this thread.

    I'll just say again that I'm sorry for your troubles.  OpenAccess (coming with Sitefinity 4.0) will help definitely provide some innovations on this front. 

    Gabe Sumner
    http://www.sitefinitywatch.com/
  3. Georgi
    Georgi avatar
    3583 posts
    Registered:
    28 Oct 2016
    16 Dec 2008
    Link to this post
    Hello,

    We will surely research on this and see what are the reasons for this.
    As for the normalization, we will try to keep this in mind closer with the OpenAccess.

    Sincerely yours,
    Georgi
    the Telerik team

    Check out Telerik Trainer, the state of the art learning tool for Telerik products.
  4. fregas baratis
    fregas baratis avatar
    185 posts
    Registered:
    14 Nov 2002
    16 Dec 2008
    Link to this post
    Hello,

    Thanks for the replies, both here and on my blog.

    My annoyance is less with the issues surrounding Nolics, which I understand is partially out of Telerik's control.    My annoyance is more so with the general thought process at Telerik and other vendors about a science and best practices that's literally decades old.  This is not new technology or methodology.  It should be a given.  Any university database class or "introduction to database design" will teach you how to normalize your data and 3NF.

    I'm sorry to be on a high horse but  this has been a problem for us several times.  At one point someone at Telerik told me they denormalized certain tables "because it was more flexible."  I think this was pure marketing BS.  I have never ran into a situation where I couldn't achieve something using normalization.  If anything, breaking your data up and preventing duplication makes your application more flexible and more maintainable.

    So I hope this mindset will be resolved and I do hope that OpenAccess makes it easier for you guys to implement it.

    Thanks for listening,
    Craig
  5. Georgi
    Georgi avatar
    3583 posts
    Registered:
    28 Oct 2016
    17 Dec 2008
    Link to this post
    Hello,

    Thank you for your additional notes.
    I noticed that you mentioned about the processes, best practices and science approaches we do here at Telerik, as well as for someone else in other companies - every tech.design have weaknesses, the Normalization has too. Do you think we do not know how to normalize our databases? In fact we have spent quite long time in researching about the ORM some years ago, and how to build the database with it in order to achieve maximum performance. If you build a web site having several millions impressions in the work hours of the day, sometimes you may sacrifice the normalization to gain some database performance. One more thing (which affects the performance) - we noticed that the people who like to take a look at the database tell, that even now there are too many relations. It seems not everybody likes the join statements :)

    I can still completely understand how you feel about the things your are talking about. We made some notes again, as always, and will have them in mind when migrating to the new ORM.

    All the best,
    Georgi
    the Telerik team

    Check out Telerik Trainer, the state of the art learning tool for Telerik products.
  6. fregas baratis
    fregas baratis avatar
    185 posts
    Registered:
    14 Nov 2002
    17 Dec 2008
    Link to this post
    Hi Georgi,

    First off, let me say that I don't want this to degrade into a flame war or a "telerik bashing" post.  I respect your company, and I can tell that your business people and developers have learned a lot over the years and have listened to their customers.  I've always said that the very fact that you had the courage to rewrite your product and decided to use an ORM shows maturity on the part of your development team.  I also realize that developers (including myself) are opinionated and have different concepts of what are best practices and the best way to develop software.  So please don't take this as a personal attack on you or the company.

    All that being said, I don't think performance was the reason for the denormalization of certain tables in Sitefinity.  I can't speak for your developers, so I don't know if it was ignorance or a tight deadline, or what the motivation was. Specifically the issue that spawned this thread, by placing the text "UpcommingEvent" over and over in multiple rows (denormalizing) could have been avoided by normalizing and putting just the value, and caused zero performance impact.  Also, I had at one point on this forums questioned the wisdom of the "meta-data" approach to modules where field values for a module are stored not as columns in a single row, but instead broken out into seperate rows.  (There are other ways to acomplish this.)  Telerik's response was that this was more flexible and worth the impact on performance and scalability so that developers could add new fields to the module without making code changes.  Furthermore, sitefinity (or Nolics) hammers our databases with queries thru what I suspect is from unnecessary lazy loading.  At one point, i ran query analyzer on a page that displayed events, and to show 10 events Sitefinity issued something like 50+ queries.  This is a common mistake when using any ORM, and a few more joins to get the data at once rather than seperate queries would actually increase performance.  Making multiple queries to the database has magnitudes more performance impact then a simple join does.  If Telerik was overly concerned with performance they improve it easily simple by reducing the number of seperate queries that fire.  

    Yes there are times to denormalize a table.   Yes there are downsides to normalization. But my experience has been that the benefits outweigh the costs.  If you have a log or "write only" table that NEVER gets updated/deleted you can denormalize.  If you have reports that need to run you might make a seperate reporting table that is denormalized and optimized for reporting.  But you should always have a single point of record for a single piece of data that needs to be inserted, updated and deleted, and use the normalized data to refresh the reporting data.  Furthermore, the development community I respect, those that practice things like OOP, design patterns, Domain Driven design, TDD, etc. generally all normalize their databases.  This makes me think this is less of a personal opinion and more of best practice that should be industry standard.  

    Its certainly possible to "over-normalize" as well and I think I have seen places in Sitefinity with signs of that, so I understand other developers complaints in that regard.  I guess my rule of thumb is that if your are repeating yourself (repeating data) then you should normalize and use a foreign key.  You shouldn't break up tables just for its own sake, but in order to prevent duplicates and have a single place to update a single piece of data.  The DRY principle is my guide in this regard, whether is data, C# code, markup, documentation, etc.  

    Anyways, I hope this is constructive critcism and thanks for listening.
  7. Georgi
    Georgi avatar
    3583 posts
    Registered:
    28 Oct 2016
    17 Dec 2008
    Link to this post
    Hi,

    I would like to thank you for the answer.

    Of course we are accepting everything as a constructive feedback.
    One of the things that are going to be changed with the new ORM is the meta key approach. This is going to happen for sure.

    The bottom line is that we made some conclusions and will try to optimize the database in a better way very soon.

    All the best,
    Georgi
    the Telerik team

    Check out Telerik Trainer, the state of the art learning tool for Telerik products.
Register for webinar
7 posts, 0 answered