Lookup List of Years



  • This table exists in a database I inherited:

    CREATE TABLE [dbo].[tllYears](
    	[yearID] [tinyint] NOT NULL PRIMARY KEY CLUSTERED,
    	[strYear] [varchar](100) NOT NULL
    GO
    
    INSERT [dbo].[tllYears] ([yearID], [strYear]) VALUES (1, N'1930')
    INSERT [dbo].[tllYears] ([yearID], [strYear]) VALUES (2, N'1931')
    INSERT [dbo].[tllYears] ([yearID], [strYear]) VALUES (3, N'1932')
    INSERT [dbo].[tllYears] ([yearID], [strYear]) VALUES (4, N'1933')
    INSERT [dbo].[tllYears] ([yearID], [strYear]) VALUES (5, N'1934')
    --snip--
    INSERT [dbo].[tllYears] ([yearID], [strYear]) VALUES (81, N'2010')
    INSERT [dbo].[tllYears] ([yearID], [strYear]) VALUES (82, N'2011')
    INSERT [dbo].[tllYears] ([yearID], [strYear]) VALUES (83, N'2012')
    

    and was created by a former colleague in all seriousness. I keep it in there like a head on a spike.

    There is, as you probably guessed, another table that used to have a field that looked up (in the front end) the values of yearID and displayed strYear. It was of course not constrained to it, which at least made for one less step in the process of making it sane.

    I think it's quite a neat package of a few WTFs.



  • I felt like this was coming from your post in another thread.



  • Why start in 1 and in 1930? Why a varchar when a small int could handle it? Why does this even exists?



  • @Eldelshell said:

    Why a varchar when a small int could handle it?

    A small int could cause problems around Y32K or Y64K. This code is ready for Y100K!


  • kills Dumbledore

    varchar(100) means it's not YGoogol ready. They should sort that



  • @chubertdev said:

    I felt like this was coming from your post in another thread.

    It is a bit like an antonym to that (a table where one isn't required) and a synonym in that you can't understand the data without the front end or vice versa.



  • @Eldelshell said:

    Why ...? Why ...? Why ...?

    Why is always the hardest question to answer.


  • kills Dumbledore

    I just noticed, the column is VARCHAR, but each string value is using the N prefix to force it as NVARCHAR. This is a well thought out table



  • Either someone tried (and failed) to make a data warehouse-y date table, or they wanted to constrain the selectable years, or they're TRWTF. I vote for #3.



  • @jaloopa said:

    each string value is using the N prefix to force it as NVARCHAR

    That's a relic of the SQL Management Studio script generator, it sticks N's in front of every string just in case. I couldn't be arsed to remove them like some of the other stuff it added.
    I doubt this table was created with a script. Those 'numbers' were probably entered by hand (and amended by hand a few times as 2009 became 2010, etc.).



  • @VinDuv said:

    A small int could cause problems around Y32K or Y64K. This code is ready for Y100K!

    Sadly not as the primary key is a tinyint. It's not even Y22K compliant



  • @LurkerAbove said:

    It is a bit like an antonym to that (a table where one isn't required) and a synonym in that you can't understand the data without the front end or vice versa.

    It's usually funny that you can tell if the DB/app code was developed by one person, or at least two people, since the WTFs usually persist on both sides if it's the former.



  • @VinDuv said:

    A small int could cause problems around Y32K or Y64K. This code is ready for Y100K!

    The primary key is a tinyint. There's no way to have more than 255 records in the table. So, they have a Y2185 problem.



  • I'm still trying to figure out why anyone would ever make a primary key a tinyint. The typical justification for tinyint is that a tinyint is smaller than an int. But, since it is limited to 255 values, the maximum savings is 765 bytes. tinyint is slower than int, and obviously has range problems, so I just can't find anything that would cause even a slightly insane person to choose it. This guy had to be completely insane.



  • In this, yes. If you have a lookup table with an insanely unlikely probability with more than 100 values, it makes sense.



  • Well, duh, embedded systems without a file system might greatly benefit from saving 765 bytes!



  • It doesn't allow negative values so in some situations that could be a useful (if lazy) way of constraining it (if you were sure no more than a few records would ever be needed). It can of course be changed at a later date so it's the least of the WTFs really. But why choose it for this? I think for some misplaced ideas about optimisation (that the varchar(100) field totally annihilates). When it's a key that a field in another table constrains to then the space savings could become significant if that table had a lot of records (both fields have to be the same datatype). But in this case it didn't and was never likely to.



  • In case anyone isn't aware: it's not just that the table is very badly designed (in every possible way really - and it's not really been lazily/hurriedly put together as the deliberate selecting of the tinyint field shows); it should not exist at all.

    The other table should store the year as a year (smallint). The front end should constrain the values that can be put in that field as needs be. Having a table-valued function that would dynamically update the allowed values as the years progress would be the best way, I think, if that needs to be adaptable with a centralised administration.


  • Discourse touched me in a no-no place

    @mott555 said:

    Well, duh, embedded systems without a file system might greatly benefit from saving 765 bytes!

    Especially if they're running Oracle!


  • Java Dev

    Oracle doesn't have tinyint. It only has NUMBER (a base-100 format), BINARY_FLOAT, and BINARY_DOUBLE.



  • @LurkerAbove said:

    It doesn't allow negative values so in some situations that could be a useful (if lazy) way of constraining it (if you were sure no more than a few records would ever be needed).

    That got me when I ported an internal app from MySQL to MSSQL. All integer types in MySQL can be either signed or unsigned. I had a signed tinyint that took a bit of figuring why it didn't work anymore. I'm sure we can figure out a way to blame PHP here...



  • @LurkerAbove said:

    and was created by a former colleague in all seriousness. I keep it in there like a head on a spike.

    Been here, done that.. Also, ours had a bonus WTF of being - in order - not Y2K10 compliant, not Y2K11 compliant, not Y2K12 compliant, and all the way to 2015 - at least until somebody came and generated a few rows each year.



  • I remember seeing that and it's a monster of a WTF table. This is but a baby kobold in comparison.

    The rows in this had to be updated each year too: the field in the other table should not allow future years so, obviously, the best way to do that is to restrict it to selecting from a table that doesn't have future years.



  • @LurkerAbove said:

    This is but a baby [s]kobold[/s]troglodyte in comparison.

    FTFY.

    Filed under: aren't baby kobolds cute? ;)


Log in to reply