database design asking for advice

Question

I need to store entries of the schema like (ID:int, description:varchar, updatetime:DateTime). ID is unique primary key. The usage scenario is, I will frequently insert new entries, frequently query entries by ID and less frequently remove expired entries (by updatetime field, using another SQL Job run daily to avoid database ever increasing). Each entry is with 0.5k size.

My question is how to optimize the database schema design (e.g. tricks to add index, transaction/lock levels or other options) in my scenario to improve performance? Currently I plan to store all information in a single table, not sure whether it is the best option.

BTW: I am using SQL Server 2005/2008.

thanks in advance, George

Answer 1

Your decision to store everything in a single table needs to be reviewed. There are very few subject matters that can really be well modeled by just one table.

The problems that arise from using just one table are usually less obvious than the problems that arise from not creating the right indexes and things like that.

I'm interested in the "description" column (field). Do all descriptions describe the same kind of thing? Do you ever retrieve sets of descriptions, rather than just one description at a time? How do you group descriptions into sets?

How do you know the ID for the description you are trying to retrieve? Do you store copies of the ID in some toher place, in order to reference which ones you want?

Do you know what a "foreign key" is? Was your choice not to include any foreign keys in this table deliberate?

These are some of the questions that need to be answered before you can know whether a single table design really suits your case.

Answer 2

It sounds as simple as it gets, except for possibly adding an index on updatetime as OMax suggested (I recommend).

If you would also like to fetch items by description, you should also consider a text index or full-text index on that column.

Other than that - you're ready to go :)

Answer 3

Your ID is your primary key and it has automatically an index. You can put onther index for the expiration date. Indexes are going to help you for searching but decreases the performance when inserting, deleting and updating. Anyway one index is not an issue.

It sounds for me somehow strange -I am not saying that it is an error- that you have ALL the information in one table. Re-think that point. See if you can refactorize something.

Answer 4

Additionally to your primary key, just add index on updatetime.