What's the best way to store a title in a database to allow sorting without the leading "The", "A"

Question

I run (and am presently completely overhauling) a website that deals with theater (njtheater.com if you're interested).

When I query a list of plays from the database, I'd like "The Merchant of Venice" to sort under the "M"s. Of course, when I display the name of the play, I need the "The" in front.

What the best way of designing the database to handle this?

(I'm using MS-SQL 2000)

Answer 1

Store the title in two fields: TITLE-PREFIX and TITLE-TEXT (or some such). Then sort on the second, but display the concatenation of the two, with a space between.

Answer 2

My own solution to the problem was to create three columns in the database.

article   varchar(4)
sorttitle varchar(255)
title     computed (article + sortitle)

"article" will only be either "The ", "A " "An " (note trailing space on each) or empty string (not null)

"sorttitle" will be the title with the leading article removed.

This way, I can sort on SORTTITLE and display TITLE. There's little actual processing going on the computed field (so it's fast), and there's only a little work to be done when inserting.

Answer 3

I agree with doofledorfer, but I would recommend storing spaces entered as part of the prefix instead of assuming it's a single space. It gives your users more flexibility. You may also be able to do some concatenation in your query itself, so you don't have to merge the fields as part of your business logic.

Answer 4

You are on the right track with two columns, but I would suggest storing the entire displayable title in one column, rather than concatenating columns. The other column is used purely for sorting. This gives you complete flexibility over sorting and display, rather than being stuck with a simple prefix.

This is a fairly common approach when searching (which is related to sorting). One column (with an index) is case-folded, de-punctuated, etc. In your case, you'd also apply the grammatical convention of removing leading articles to the values in this field. This column is then used as a comparison key for searching or sorting. The other column is not indexed, and preserves the original key for display.

Answer 5

I don't know if this can be done in SQL Server. If you can create function based indexes you could create one that does a regex on the field or that uses your own function. This would take less space than an additional field, would be kept up to date by the database itself, and allows the complete title to be stored together.