Using a Sequence instead of a year number

by Alf

Using a Sequence instead of a year number: I just have to tell you this one:

While working as a DB QA manager in a large DB project, upon revision of the design I discovered that the contracting consultant company (big and French) had a policy of using sequences (auto-generated numbers) for all primary keys in tables.

What was funny was that they used a sequence to uniquely identify the YEARS table (It contained 2 columns; Year number (4 digits) and Active (Y/N).

They actually used a sequence to uniquely identify the year (2007, 2008, etc.) which forced the database to make yet another join in the database for EVERY query just to find what year we were talking about. Crazy!

The worst part? When they realized the idiotic design, it took them one year and another 60 man-years to rewrite the application...

This was an application that was supposed to handle 350-500 mill. transactions/year...

What is YOUR worst database design experience? PLEASE share it right here!: