Efficiently finding unique values in a database table

Question

I've got a database table with a very large amount of rows. This table represents messages that are logged by a system. Each message has a message type and this is stored it it's own field in the table. I'm writing a website for querying this message log. If I want to search by message type then ideally I would want to have a drop down box listing the message types that have come up in the database. Message types may change over time so I can't hard code the types into the drop down. I'll have to do some sort of lookup. Iterating over the entire table contents to find unique message values is obviously very stupid however being stupid in the database field I'm here asking for a better way. Perhaps a separate lookup table which the database occasionally updates listing just the unique message types that I can populate my drop down from would be a better idea.

Any suggestions would be much appreciated.

The platform I'm using is ASP.NET MVC and SQL Server 2005

Answer 1

A separate lookup table with the id of the message type stored in your log. This will reduce the size and increase the efficiency of the log. Also it would Normalize your data.

Answer 2

Yep, I would definitely go with the separate lookup table. You can then populate it using something like:

INSERT TypeLookup (Type)
SELECT DISTINCT Type
FROM BigMassiveTable

You could then run a top-up job periodically to pull in new types from your main table that don't already exist in the lookup table.