Tuesday 17 April 2012

Ascii a simple question and you'll getti a simple answer!

In a comment on the last post Fed questions why we need bother with the 'xID' column in a DB table. I'm assuming that he's working on the basis that, taking the Sport table as an example, 'Football' is unique and different from 'Cricket', and 'Cricket' from 'Golf' and so on ad infinitum. The sportID column, therefore, would appear to be redundant.

Fed is right. There is no reason why you can't form this DB with a single column table for Sports, Strategies, Markets Traded or a host of other 'descriptive' tables as long as the individual entries are unique.

There are, however, many key reasons why you shouldn't do this:


  1. Access will work a lot quicker working with a column of numbers
  2. Computers don't store letters as letters - they store them as number using the 'ASCII' coding usually, so an 'A' is stored as ASCII code 65 and 'a' is ASCII 97 - so why complicate things??
  3. Later, when defining relationships in a db these need to based on the same data types - so you can't relate an integer with a textual string for example
  4. Storage reasons - storing a Long Integer autonumber takes 4 bytes of hard disk no matter how big the number gets. Storing the city name 'Ely' requires 3 bytes, whilst if you need to store that Welsh town Llanfair........gogogoch you'd need over 50 bytes of storage! Ok once but what if it crops up 1,000 times in your db - 4,000 bytes for storing a number and over 12 times that needed to store the actual name that many times
  5. Because every book / web article on good database design theory says you shouldn't!


Hopefully that makes it clear.

No comments:

Post a Comment