There are those who believe that each and every table in a relational database must have a "uniquifier" Primary Key (IDENTITY, SEQUENCE, GUID, and so on) and there are those who actually know how to design a relational database. As Dave points out, the trouble comes when the former group is actually allowed to design databases.

Day one in a new contract, with a large multi-national bank, and a little bit of digging in the database for a business-critical application uncovered the wonderful table tDayOfMonth:

tDayofMonth
DayofMonthID
(IDENTITY)		 DayofMonth
(VARCHAR(50))
1 01
2 02
3 03
...
29 29
30 30
31 31

Inspired by this, I dug a little further and found tMonthOfYear (I'm sure I don't need to tell you what that was all about) and tYearNumber:

tYearNumber
YearID
(IDENTITY)		 YearNumber
(VARCHAR(50))
1 1900
2 1901
3 1902
...
159 2058
160 2059
161 2060

Note the nice use of an arbitrary offset of 1899; a security feature perhaps? This design also offers us the flexibility to update the year "1986" to "orange." Oh, and for good measure, there was also the tYearNumber_1 table; it was exactly the same.

But things got better. Take, for example, tDecisionType:

tDecisionType
DecisionTypeID
(IDENTITY)		 DecisionTypeName
(VARCHAR(50))		 DecisionTypeDscp
(VARCHAR(50))		 SortID
(INTEGER)
1 Yes Yes NULL
2 No No NULL

And of course, what system designed along these lines would be complete without tBoolean ...

tBoolean
BooleanID
(INTEGER)		 BooleanName
(VARCHAR(50))
0 No
1 Yes

I presume the only reason this isn't defined as an identity like the others is that they couldn't work out how to seed it with a zero. And, just to round off this WTF, there's even a tWF table.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!