Hi guys,

I am creating a database for a university project and have a query about one aspect of normalisation.

I have a few tables that have a date as a field (news, guestbook entries etc). Is it good practice to separate this date into a new table called 'Date' and therefore have only have one instance of each date and a reference through foreign key to the relevant one?

I have never bothered in previous databases but now that i'm thinking through the normalisation process more rigidly ive come to think it may be the correct thing to do - at least theoretically.

Any thoughts are appreciated!

Cheers
Stoot

I haven't been creating databases for a real long time, so take what I have to say with a grain of salt. Personally, I don't think I would normalize a date field, especially when you have the date applied to completely different types of data like news and guestbook entries. If you're using any sort of time stamp, it wouldn't make sense because no two entries would share the same date. As I understand normalization, the whole purpose is to optimize the database by cutting down on duplication. If no duplication occurs, however, you're just creating extra work for yourself.

If you're using any sort of time stamp, it wouldn't make sense because no two entries would share the same date.

Exactly. Usually, a timestamp store the time with a millisecond precision.
Putting those in a separate table with FK referencing them will add nothing but overhead.
Your "time" table will grow, and each time you will need to look up for that time, you will have to scan the indexes.

And a simple reminder: the more datas a table contains, the more time the indexes needs to be parsed. It can reache a state where a sequential read is faster than an index scan.
http://www.mysqlperformanceblog.com/...-where-column/

The reason why index scan is slower than table scan is that in case of the index scan the MySQL needs to perform more operations like :
read index, get pointer to row, get data from row,
that adds overhead to the operations.
In case of table scan MySQL just goes trough the table and read all rows in continuous mode.
When count of scanned rows more than 20% (in our example) the overhead of index scan makes it non-effective.
In real queries the right percent of rows depends on many factors, but for random uniformly distributed values I would say it is in 20-30% interval.

It should not be the case if you normlalize your dates, because no entries would be similar, but still, many peoples don't realize that.
I learned it not so long ago myself.

In the case of a timestamp, I almost always find it to be relative to the information I write in my table, so I keep them together.

Thanks for the replies!

I agree with you both about the timestamping. I wasn't thinking of them in terms of timestamps when i made the post but just as simple dates (e.g. 01/01/2008) however, i think it would probably be beneficial to have them store a time and a date, so ill just leave them together.

Anyway, that saves me writing a bit about how i normalised the dates!

Cheers
Stoot

I would normalize events, but not dates. If lots of things correspond to the same point in time, something important enough to earn a name, that makes sense to describe in terms of PK and FK relationships. Say an election, or a software release.

Originally Posted by tripy

Your "time" table will grow, and each time you will need to look up for that time, you will have to scan the indexes.

If you're seeing a lot of index scans in your query plans, something is wrong in the design. You should see mostly index seeks, otherwise your system isn't performing as well as it could, and needs more expensive hardware to support it than absolutely necessary.

Originally Posted by tripy

And a simple reminder: the more datas a table contains, the more time the indexes needs to be parsed. It can reache a state where a sequential read is faster than an index scan.

This is true. It isn't just tables growing to hold more data over time that causes this. Any time the cost of traversing the index is more than the benefit of using it, you have this sorry state of affairs.

Plus, every time you change data on a keyed field, the index(es) that reference it are updated as part of that transaction. So delete, insert, and update commands all slow down for the presence of an index. Unless, of course, the update benefits from being able to find the row(s) to change.