database design asking for advice

views:

126

answers:

database design asking for advice

Hello everyone,

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

+3 A:

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

OMax 2009-06-02 08:23:58

Just that simple? No additional tricks? :-)

George2 2009-06-02 08:45:04

+1 A:

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.

Luixv 2009-06-02 08:29:32

Thanks, 1. what is your advice about index? Making another index on Expire date or? 2. Could you show me more insights about why I should contain all data in more than one table?

George2 2009-06-02 08:46:16

Sure. A second index on expiration date is adviced. Consider to refactor data. If at some column any data is repeated (it is an enumeration) the you could have another table with this data. For example a field country. Then you can have at the main table only the country code and in another table the list of countries. The main table will be smaller because you are not saving repetitions only foreign keys.

Luixv 2009-06-02 09:15:35

Thanks @Luixv! Store everyting in one table is fine? What I store is user activity (log) on my web application.

George2 2009-06-02 10:07:51

I think it is OK what you are doing. The only possible point for saving the info in different tables is to have a secondary table with the name of the page the request has been sent (i.e. index.html).Anyway, I don't think it makes and major difference.

Luixv 2009-06-02 10:12:18

@Luixv, any benefits we could have when using a secondary table as you suggested? I think using another table will degrade performance.

George2 2009-06-02 11:04:26

I wouldn't use a secondary table for your problem. I don't think you will need it. Your problem is just strightforward.

Luixv 2009-06-02 12:01:50

+1 A:

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 :)

Roee Adler 2009-06-02 08:30:39

Store everyting in one table is fine? What I store is user activity (log) on my web application.

George2 2009-06-02 08:46:59

If the table is so simple, I say yes. For large text fields, the database stores them in a separate data source either way, so splitting the table just for the text field won't add anything.

Roee Adler 2009-06-02 08:52:03

Two more comments, 1. " a separate data source" -- you mean something other than database (like file system?) or another database table? 2. "splitting the table just for the text field won't add anything" -- confused about what you mean, why splitting does not help if text column is big? Could you express your points in some other words please?

George2 2009-06-02 10:09:26

+2 A:

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.

Walter Mitty 2009-06-02 10:13:26

Hi Walter, ID is just incremental integer, description is the title of web page currently -- so one description at a time, and each time, I only need to retrieve one of them randomly by ID column exact match (no need to retrieve all of them). Using foreign is good design in general, but I have not forseen any needs in my current requirements. Let me know if you need more information to give me good advice, thanks!

George2 2009-06-02 11:03:31

OK, it sounds like the description values are all of the same kind, so it's okay to put them all in the same column. The technical jargon is "all drwan from the same domain". ID and Updatetime seem to belong in the same table. So I guess you don't need more than one table. Now my question is, why do you need a database? Can't you just store these three fields in an indexed file?

Walter Mitty 2009-06-02 14:20:52

"drwan" should be "drawn". Doh!

Walter Mitty 2009-06-02 14:21:26

ansaurus

tags:

views:

answers:

database design asking for advice

related questions