Normalize dates into there own table. Is there any point?

[DelboyDee]

Hi all,

I have a table that contains a DateTime column. The structure is as follows:

DateTime LogTime
long AreaID
string Message

The Dates are not unique as the same date is guarenteed to occur say 95 times as there are 95 areas and I expect 1 message per minute per file to be imported into the database. So there are 1440 records added per file per day which is a total of 136800 records added per day for all areas!

My question is, should I split out the dates into their own table as they are repeated 95 times for every minute of the day? Is there any benefit in this specific normalization?

I have tried to do this split but I spend 5 seconds per day looking up the ID for a specific date for every file so that the data can be imported. This amounts to waiting an extra 8 minutes to import all of the data into the database all because I have to convert the DateTime to its respective ID value in the other table!

Is the size of the DateTime variable bigger than that of an int in the database?

If I move to SQL server 2000 which im in the process of doing so does any of this matter as the database size will no longer be an issue? So should I concentrate more on execution times?

Thanks for you time :-)

Delboy

[ITGURU]

I think it will not be beneficial for normalize your table, because you also saving time with date which is not unique in a day.

So try to concentrate on Execution time instead of database size.

[DelboyDee]

Thanks for the advise ITGURU. I have created a table with the following fields now.

DataID Autonumber
Date DateTime
AreaID Number
String Message

I have set up the Date and AreaID fields to be unique when combined (i.e. the date will not occur again for a specific area)

The import code execution time is now down from 50 seconds to 4-5 Seconds! ;-)

Thanks for you help.

Del