How to keep track of column updates?

Question

I'm using SQL Server to store users uploaded songs.

Each song has an id(simple auto increment number) and a name.

I want to use URL Rewriting to query some song, like this:

http://wwww.mysite.com/song/song_name

The problem is that I also want to enable users to change their songs name and still use the old URL.

I thought of creating another table whice will contain the old names and then check for some song name in both tables, is that a good solution?

Answer 1

A better approach may be to create a single URL table that contains the URLs and the song it's tied to:

SongID:   URL:
1         song_name 
1         song_name_change
2         other_song_title

This approach would:

1. store as many URLs for each song as you need
2. simplify your query
3. potentially speed up your query by storing URL-formatted strings your your database (you mentioned you're just storing the song id and name, so I'm assuming you're formatting all titles like 'Song name' to 'Song_name' on-the-fly in the query)

Answer 2

Additionally to the existing songs table I would:

• create a table alias with columns
  • alias - would contain song names, original and modified
  • target - id of the song, reference to songs table
• optionally, add the column main_alias to songs table; it would reference the main alias (i.e. the last edited one), so that when You can redirect people using old aliases or going to the song via its id.

Then, You could access the song via

• URL/[song_id] - find song by id + rewrite the URL to main alias
• URL/old_song_name - find song by alias + rewrite the URL to main alias
• URL/new_song_name - same as above
• URL/even_newer_song_name - same as above

All in all, you can have unlimited number of names pointing to the same song.

Answer 3

Create table song_names with fields

• id
• song_id (key to songs table)
• name (name of the song, perhaps unique)
• creation_date (datetime when this name was created)

If you get a name of the song from link, you can find it in this table, so you can find a song in songs table. The actual song name will be the one in song_names table with given song_id and most recent creation_date.