Should I use an auto-generated Primary Key if I'm just doing a lookup table?

Question

I have a table which has two varchar(Max) columns

Column 1      Column 2
-----------------------
URLRewitten   OriginalURL

its part of my url re-writing for an asp.net webforms site.

when a url comes in I do a check to see if its in the table if it is i use the OriginalURL.

My question is, if all I'm doing is querying the table for urls and no other table in the database will ever link to this table does it need a dedicated primary key field? like an auto-number? will this make queries faster?

and also how can I make the query's run as faster?

Edit: I do have a unique constraint on URLRewitten.

Edit: ways i'm using this table..

• Query when a new Request comes in.. search on URLRewitten to find OriginalURL
• When needing to display a link on the site, i query on the OriginalURL to find the URLRewitten url i should use.
• When adding a new url to the table i make sure that it doesn't already exist.

thats all the querys i do.. at the moment.

Both columns together would be unique.

Answer 1

You don't NEED one, but it is best to have one in your table for future scalability. Even though you said you won't expand the usage of the DB further, it's good to be prepared.

Answer 2

Do you need a primary key? Yes. Always. However, it looks like in your case OriginalURL could be your primary key (I'm assuming that there wouldn't be more than one value for URLRewritten for a given value in OriginalURL).

This is what's known as a "natural key" (where a component of the data itself is, by its nature, unique). These can be convenient, though I have found that they're generally more trouble than they're worth under most circumstances, so yes, I would recommend some sort of opaque key (meaning a key that has no relation to the data in the row, other than to identify a single row). Whether or not you want an autonumber is up to you. It's certainly convenient, though identity columns come with their own set of advantages and disadvantages.

For now I suppose I would advise creating two things:

1. A primary key on your table of an identity column
2. A unique constraint on OriginalURL to enforce data integrity.

Answer 3

An identity column can help when you search for recent events:

select top 100 * from table order by idcolumn desc

We'd have to know what kind of queries you are running, before we can search for a way to make them faster.

Answer 4

I'd put one in there anyway... it'll make updating alot easier or duplicating an existing rule...

i.e. this is easier

UPDATE Rules SET OriginalURL = 'http://www.domain.com' WHERE ID = 1

--OR

INSERT INTO Rules SELECT OriginalUrl, NewUrl FROM Rules WHERE ID = 1

Than this

this is easier

UPDATE Rules SET OriginalURL = "http://www.domain.com" WHERE OriginalURL = 'http://old.domain.com'

--OR

INSERT INTO Rules SELECT OriginalUrl, NewUrl FROM Rules WHERE OriginalURL = 'http://old.domain.com'

In terms of performance, if your going to be searching by OriginalURL, you should add an index to that column,

Answer 5

As you are doing your query on the URLRewritten column I don't think adding an auto-generated primary key would help you.

Have you got an index on your URLRewritten column? If not, create one: that should see a big increase in the speed of your queries (perhaps just make URLRewritten your primay key?).

Answer 6

I don't think adding auto generated primary key will make your query faster.

However there are are a few things to consider:

1. I would not be so sure, that never ever nothing will link to this table :(.
2. I've seen a lot of people asking about how to i.e. remove duplicates from table like that -- with primary key it is much easier.
3. To make this query faster we need to know more about this table and ways of using it...

In my opinion, every table, must have auto generated primary key (i.e. identity in MSSQL).

I don't believe in unique natural keys.

Answer 7

Yes there should be a Primary Key Because you can set INDEX on that Primary Key for Fast Access

Answer 8

I would use the OriginalURL as your primary key as I would assume this is unique. Assuming your are using SQL-Server you could create an index on RewrittenURL with OrigionalURL as an "Included column" to speed up the performance of the query.