… Can Be Complicated

It’s true, as mentioned in this article that pretty URLs have many benefits.

However, by reading such articles one gets the impression that converting a site with URLs such as http://www.yourdomain.com/catalog.php?manufacturer=2&category=5&subcategory=3&product_id=93 into a site that uses URLs like http://www.yourdomain.com/catalog/spacely-sprockets/8-inch/16-tooth/90325M/ is a very easy thing; after all, you simply need to create a .htaccess file and make the correct substitutions and your in business, right?

The truth of the matter is that it’s a bit more complicated than simply creating the substitution rules. This only takes care of converting what is supplied by the browser into what is needed by the application and does not cover what is required to make the application output those pretty URLs in the first place. Without this, those pretty URLs (and the .htaccess file) are pretty useless.

Articles such as the one mentioned above are great for the developer that’s planning a new application and wants to employ this type of URL schema from the beginning. On the other hand, converting an existing site can be a challenge.

As a developer, I’m frequently asked to come up with a quote on how long it will take to do such conversions. More often than not I cannot even begin to evaluate these projects because I don’t have the information I need.

Below I will attempt to explain what information a developer really needs (what I really need) to quote such a project and why that information is important as well as some of the complexities in the conversion itself.

When having such a project estimated you should always be a little leery of a development company that doesn’t ask for this type of information. Without it there is a good chance the project is being grossly under of over estimated. If I must quote such a project without the needed information then I must assume the worst case scenario and quote accordingly. I don’t like making such quotes because without looking at what I need to do there is no way for me to know if the conversion can be accomplished.

What Information is Needed?

Generally, before I will begin to work on evaluating a project like this I will ask for the following:

1. The platform that site is running on
2. Access to the database associated with the application used on the site
3. FTP Access to the site

What Platform Does the Site Use?

This is the most important question for me and it is usually the easiest information to obtain:

1. Is the site hosted on a Windows or an Apache server environment?
2. Does the site use PHP, Classic ASP, ColdFusion or .NET or something else entirely?
3. What type of database does the site use?

As a developer, in order to be able to evaluate a project I must have enough expertise in the technology used. The reason for this is that I will need to look at the work done by another developer and I must be able to understand it well enough to figure out what will be involved in completing the project. I’m sure other developers may view this differently, but I personally will not even attempt to quote a job that I feel is beyond my areas of expertise. Doing so amounts to making a promise that I can’t guarantee I can keep.

Why do I need access to the database?

Next to the site’s platform, this is the most important thing I need to have. A database is like the foundation of a house. This is the base that a site is built on and without understanding how the database is designed I cannot even begin to guess at what work will be involved in making changes to the site that it supports. Just like a house, you can not put additions on where there is no foundation, before adding that addition you must first understand and possibly alter that foundation.

Also, again going back to the house metaphor, if you have a well designed foundation it will last and allow for easy remodeling. On the other hand, a poorly designed foundation may mean that remodeling isn’t possible and that the house will not stand as long.

What am I looking for in the Database?

1. Database Design
2. Uniqueness of Data
3. Use of Special Characters in Data

Database Design: Mostly I am looking to see if I can make sense of the data stored in the database simply by looking at the table and column names. Did the designer use names that relate to the data, for example, are the tables named things like “manufacturers”, “categories”, “subcategories” and “products” or do the tables have obscure names like “table_1″, “bob” and “data”. I also need to know how the various tables are related. If this is easy to figure out and I can find the data I’m looking for, then I can move on to the next step.

On the other hand if things are too obscure then I’ll need to reverse engineer the database before I can go any further. Reverse engineering a database built by someone else can be a time consuming process. At this point I must do one of two things, quote a rough estimate of how long I think it will take me to do this reverse engineering or go back to the worst case scenario to quote the entire job. In the first case this means that the client must pay to have work done that may, in the end, result in no results, because at this point we still don’t know what the final estimate will be or even if the project can be accomplished, we’re simple digging deeper. In the latter case the quote is likely to be high and again, we still don’t know if we can make the needed changes.

Uniqueness of Data: The uniqueness of the data that will be used in the rewrite rules is extremely important. As an example lets take a table database that looks something like this:

Please keep in mind that these are extremely simplified examples to illustrate a point. In reality, discovering if data is unique may depend on any number of factors, like related tables, which is why access to the database is important.

With the above it is relatively easy to look up the id of the category and substitute a name turning http://www.yoursite.com/products.php?category=2 into http://www.yoursite.com/products/cogs/ and then when we load the page to do the reverse substitution. Now let’s look at the following example:

Now, to create the “Pretty” URL we look up the category id and substitute a name: http://www.yoursite.com/products.php?category=4 becomes http://www.yoursite.com/products/cogs/, the same URL that we got from the first example. But now when we do the reverse substitution the result we get will likely be the wrong result since there are two categories with the same name. In the case where uniqueness does not exist it will be necessary to extend the database, creating a new field that will contain the unique information we need.

Use of Special Characters in Data: There are many characters that are available on a keyboard that are simply not allowed in a URL. Many users are simply unaware of this and many developers, when building a system that will not use pretty URLs, don’t take this into consideration when deciding what a user will be permitted to enter. Characters like spaces, #, %, *, &, $, and ? simply do not work in URLs as they have special meaning to browsers and servers. If these characters are allowed there is one of two things that can be done:

1. If the list of special characters that are allowed is not too extensive we can do some simple replacements that can be reversed, for instance replacing spaces with dashes, but they must be replace by something because we need to be able to reverse the process.
2. If the list of special characters that are allowed is too extensive then, as with the lack of uniqueness, we will likely need to extend the database to create a new field to contain the information we’ll need.

Extending the database to eliminate the problems of non-unique data or special characters

Extending the database to create special fields to contain the data we will use in our pretty URLs can be a lengthy process. What we will need to do is create a program that will first add a new field to each of the tables we want to use, then:

1. Crawl through the database and retrieve the current information
2. Transform the information into something usable
3. Compare the new information to data already transformed, making alterations to ensure uniqueness
4. Insert this new data into the new field(s)

In some cases this is the only solution to get the unique data we need in a form that is usable for URLs. We will also need to keep in mind that once this is done we’ll also need to alter the original logic that inserts new data so that our new field is also populated correctly. This way anything that is added after the change will conform to our new data requirements.

Why do I need FTP access?

Once we’ve had a thorough look at the database and understand the sites foundation and any changes that it may require, then we need to look at the code that it supports. Some of the questions we ask when doing this are:

• Can we simply call a function that will transform the old ugly URLs into our new pretty URLs?
• Are the old URLs output from a central location or spread across large portions of the code?
• Do we need to re-factor extensive portions of the code to implement our new design?

Back to the house metaphor, can we use an existing door to get to our new room or do we need to tear out a wall first?

The only way to discover what is needed is to open up the files that run the site and figure out how it works and the time involved depends on the state the code was left in by the developer that created it. We need to locate, or at least have a very good idea of all the places where the code will need to be altered. The only way to get this information is with FTP access so that we can download all the files and get a really good look at them.

Are Pretty URLs always possible and the best solution?

There is no doubt that pretty URLs are better than the type that contains only id values. The site is friendlier, users know where they are, it’s much easier to communicate these pretty URLs to others, and they may have significant SEO value. However, they may not always be possible or the best solution.

Sometimes it simply isn’t possible. Take for example the company that uses a proprietary warehouse management system that creates the data used by the website. It may not be possible to alter such systems or the data they create.

When the conversion is possible, whether or not the change is the best solution is largely up to the client. The cost involved with converting a site may be more than the client feels its worth. In some cases it may simply be more cost effective to trash the entire thing and start from scratch. If this isn’t possible then to leave it as it is.

Unfortunately, there is simply no way of knowing what the best solution is until the problem is investigated and the only way that I can do that investigation is if I have adequate access to the information I need.