Written by:
Friday, June 20, 2008 1:33 PM 

My previous post about building a SEO DNN module had a few people asking how the various suggestions I made should be implemented. I am going to build a DNN module during the writing of this series of blog posts, and show the relevant parts of the code which achieve my SEO objectives.

Following up from the earlier post, the objectives are

-         Giving the user control over the Title, Meta Description and Meta keywords for modules that use query strings to show different content

-         Allowing the user to choose how the Url for the content is constructed

-         Using pure CSS layouts to reduce the amount of html on the page

This first post is all about giving the user control over the Url, which I suspect most people are interested in

About the Module

The new module will be called ‘Multi Text’. It’s an advanced version of the ordinary Text/Html module that just about every DNN site uses for display of static content. However, this module will allow the administrator to create several sets of content for the one page, and specify different Url values to show different content.

Basic Module Architecture

This module, like all DNN modules I build, is going to be built as a ‘Private Assembly’ module, with a data provider, business controller and user controls. It will be installed using the standard DNN module installer and be used in much the same way any other module will be used.

The Html data will be stored in a table on the database, called ‘Page’.

The table layout looks like this:

Important Points about the Table Design

There is a field called ‘UrlValue’ which will store the user-defined unique Url Value that will be used to retrieve the content from the database. Note there is no unique constraint on this field to guarantee uniqueness, nor is it the primary key of the table. This will be fully explained further in the next section.

Each of the SEO-important meta values and page title are given separate columns to store the information.

Handling Url Values

Firstly, this table will be used to store a potentially large amount of content across several portals, and on one or more pages within those portals. This is why the content is linked by a Foreign Key to ‘ModuleId’. Each ModuleId is guaranteed unique within the DNN installation, so it’s safe to use to separate out content. 

Each individual page of content is identified by the PageId, but will be retrieved from the database when the page is requested using the UrlValue column.

In order for this to work, the UrlValue needs to be unique. But it only needs to be unique across the set of records that have the same module Id.

For example, imagine we have the following records in the table (columns not shown for simplicity)

If you look through the ‘UrlValue’ column, you’ll see that there are two records with ‘First_Entry’. This is why we can’t put a Uniqueness constraint on the column, nor can we use the UrlValue column as a primary key. It’s not unique across the table, it’s only unique across the set of rows that belong to one ModuleId.

ModuleId 2 could be on one page, and ModuleId 5 could be on another page. It wouldn’t be very good if we could create http://mysite.com/Content_Page_1/First_Entry but we couldn’t create http://mysite.com/Content_Page_2/First_Entry because we had already used ‘First_Entry’ on the module on ‘Content Page 1’.

Ensuring Uniqueness on Url Value

So you can’t use a database-enforced uniqueness constraint against the Url Value to guarantee uniqueness. Instead you must write your own, logic-enforced uniqueness constraint. But there’s more to consider than just uniqueness.

The more Values Change, the more they stay the same

But there’s one more thing : people like to change the values for Urls. Perhaps the content changes, perhaps they like to experiment with keywords in the Url – whatever the reason, we as programmers need to predict and allow for this type of behaviour.

If you accept that the value is going to change, you also need to remember what all the different values have been. Imagine this series of events:

January : The website author creates new page of content and assigns the Url Value of ‘Free-Stuff’. The page is indexed by search engines, and gets plenty of visits, and a few people link to the page. Everyone is happy.

March : The website author has been doing some research, and decides that a Url Value of ‘Free-Products’ might capture more visitors and traffic, so she changes it to ‘Free-Products’.

June : After months of declining visitors, she decides ‘Free-Stuff’ was better, so she changes it back again.

There’s three potential issues here:

1. The PageRank and incoming links for the ‘Free-Stuff’ Url would be lost if the Url value gets changed to ‘Free-Products’.   The module must handle requests for the old Url, and, ideally, forward the requests to the new Url.
2. If someone else adds a new page of content to the module, and re-uses the ‘Free-Stuff’ Url (because it is now set to ‘Free-Products’, so there is no uniqueness constraint), then someone looking at an old ‘Free-Stuff’ link will get forwarded to the wrong content.
3. When it gets changed back to ‘Free-Stuff’ from ‘Free-Products’, the module must then redirect requests back from ‘Free-Products’ to ‘Free-Stuff’.

Keeping a History

The solution to these problems is the ‘PageUrlHistory’ table. The structure looks like this:

The UrlHistory table is updated every time a UrlValue is updated in the Page table. For the example shown above, you’d have the following rows in it:

While the corresponding Page record would look like this:

There’s a couple of things to cover here:

-         The original value of ‘Free-Stuff’ has been updated with a ~ to render it ‘expired’

-         The Free-Products Url is kept as history

The tilde on the ‘Free-Stuff’ (original) record is just to render it different to the unique value, so that it won’t be found. The table still keeps a record of the original though. 

Now, when a user requests a page using either of the two values (Free-Stuff or Free-Products) then the module can look up first the ‘Page’ table, then, if the Url value is not found there, it can look up the history table. Either way will identify the correct Page record for the Url. The module logic can also detect when the record was found in the history table rather than the Page table, and issue a page redirect to the latest Url value.

So, when a visitor asks for /pagename/free-products.aspx, the Page table is queried for ‘Free-Products’. Nothing is found, so the logic looks in the ‘PageUrlHistory’ table. A match is found, the page id is returned, along with the ‘new’ UrlValue of ‘free-stuff’. The visitor can be redirected towards the new value, they will never realise they clicked on an old, expired link.

Keeping it Unique

All this depends on making sure the Url Value is unique across the Module Id instance. Not only that, but the module has to handle when a website author attempts to create a new page using a Url Value that has already been used.

This is done in the module with the ‘fnUniqueUrlValue(@newUrlValue, @ModuleId)' function. This is a scalar database function which takes the prospective UrlValue and the ModuleId the new Url value is intended for (remember, the Url Value only has to be unique across the module, not the entire page, portal or DNN install).

The pseudo-code for this function goes like this:

Loop until found
    counter = counter + 1
    found = Look for urlvalue in Page table matching on moduleId and UrlValue
    if found
        result = UrlValue
    else
        found = Look for urlvalue in Page History table matching on moduleId and UrlValue
 if not found
      add ‘-‘ and append counter onto the end of UrlValue
 end loop
 

This function will take a ‘proposed’ Url Value, put it into the function, and if it is unique across the moduleId, then will just return the value straight away. If it finds another value that matches, it appends a sequence number on the end, and returns that value, which is now unique.

This means the value can still be saved to the database without a round-trip saying ‘your value was not unique, pick another value’. It just updates the value itself, and presents the new one back to the user. If they don’t like that new value, they can change it themselves to another, unique one. It’s up to the UI to inform the user of their options.

However, this function does not cover the update of the History table if the UrlValue has been updated. This is performed in the code with the stored procedure with the snappy name of ‘UpdateUniqueUrlValue’

This stored procedure has the following logic:

check if UrlValue has been used in the history table for this module
  if it has, update the old history value to invalidate it
  check and return a unique UrlValue by calling fnUniqueUrlValue
  insert the current UrlValue from the Page table as a history value
  return

So, with this stored procedure, the ‘Add’ and ‘Update’ stored procedures can now call this stored procedure to guarantee a unique value and update the history. The must also return the unique value as an OUTPUT parameter, so that the calling application can determine if an update has occurred to the supplied UrlValue.

This is done in the Add/Update stored procedures with the following code:

if page is default page for module
     set UrlValue = ‘’
  else
     set UrlValue = updateUniqueUrlValue (moduleId, UrlValue)

This set of stored procedures and functions work together to logically enforce a unique Url value. Of course, by directly editing the table contents using a table value editor or some direct Sql statements this could be rendered inoperative, but because the application will only call the database using stored procedures, this is of low concern.

Getting it Back

Retrieving the current Page record from the database using the UrlValue is as simple as looking up the Page table for the Module/UrlValue pair. If nothing is found, then look up the page history table. If the record is found, return the page record. If it was found in the history table, return another value to indicate that the history table contained the used Url, not the ‘current’ value.

Summing Up

I have attached a zip file with two SqlDataProvider script files that will be used for this module. Also in the zip file is a ‘test’ script, which you can run to see how it all fits together and works. This test script runs a series of unit tests against the code to make sure it performs in the intended manner.

Download Multi Text Test Scripts

To run the script, you will need to replace the {objectQualifier} and {databaseOwner} values with those applicable to your database, such as ‘dbo.’, and ‘dnn_’, respectively.

Questions? Comments? Let me know what you find, or tell me if you think you have found holes in the logic for the sql database.

Trackback Print

Tags:

Categories:

Location: Blogs Crafty Code

Your name:
Your email:
	(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Add Comment   Cancel