RE: FN-FORUM: Storing the leading 0 of product codes in MySQL

date posted 11th October 2006 12:15

[EMAIL REMOVED] wrote:
> Is there a reason to store it as a number? If you're not
> performing mathematics on it then it's best practice to store
> it as text (at least that's what I was told). So for example
> phone numbers should always be stored as text. Text uses
> less memory than numbers. Also why the 2 fields for product
> codes? I guess you have your reasons but it seems odd.
> Paul

New one on me. I would say the type should reflect the nature of the data.
Although a telephone number contains mostly digits it is really a text
string so that is perhaps a valid case, however anything that is truly
numeric would be best stored in a numeric field I believe, irrespective of
your application happes to currently perform any maths no it.

As far as storage space is concerned, I don't know the internal mechanics of
the various databases, but my guess would be it is close to how the computer
itself would store the value in memory - 1 byte can store 256 values for
numbers or a single digit (at very best, with ascii text) if stored as a
string. So numeric storage in that simplest example would be stored about
2.5 times more efficiently as a number (e.g. 40000 takes 5 bytes as text and
2 bytes as numeric).

As I say reality will be more complex (integer/floating point numbers,
unicode text etc.) but I find it very hard to believe that text storage of
numbers would ever be more efficient than numeric storage?

Regards,

Dai

--
TechnologyAngel
http://www.technologyangel.co.uk/