Now that Our URLs are Friendly

This post is more of a question because I really haven’t made up my mind about it yet. Maybe you can help.

Typical front-controller based web frameworks like rails and MS MVC push around URLs that might typically look like this:

This would typically show the details from a row of the products table with an identity primary key of 2 (never mind the ProductsController and the actual Product object).

Everything in this example works just fine. Of course, you’re not supposed to reveal your identities in the URL, and search engines like words, yadda, yadda, yadda… This leads to URLs that look like this:

You can tell the sorts of sites I shop at.

In our example, the product with primary key 2, has a product name of “Mace Windu Light Saber”. So assume that our Products table has an integer identity field used as the ProductId and some nvarchar ProductName.

My question is: Should the Products table also contain a column to contain the munged product name that is unique part of the URI for the coveted Mace Windu Light Saber?

The function that munges product name to URI is pretty simple. All it needs to do is replace any non-word character (\W+) with some URL friendly character (e.g., the ’-’). If you did have the URI column, it would contain redundant information wouldn’t it? It’s really a derived expression.

To perform a lookup based on the URI, you would need a function to essentially replace all those friendly characters (e.g., ’-’) with a wildcard and you’d end up with a SQL statement that has a WHERE ProductName LIKE 'mace%windu%light%saber'

We don’t have to have the URI column in the DB. The URI can be derived for writing. For lookups a similar function and some wild cards allow us to do the trick.

However, wouldn’t life be a little easier if you did have the URI column? You would only have to munge that product name once; say on the initial insert (ignoring collisions for a moment). Lookups would be a lot faster, as you can get rid of the LIKE expression, put an index on the URI column, and do a search over on an indexed column. Essentially you’d get rid of the reverse munge (de-munger?) all together. Writing the URL would be as simple as accessing another property.

You could also open up the possibility of having exceptions to your munging function. For example, if I really wanted the URL for the “Mace Windu Light Saber” to read:

that scenario is trival with the URI column.

I suppose what bothers me is having that weird URI column in my Products table. It seems to be an artifact of accessing my products over HTTP.

What do you think dear readers?