2

This is something I was pondering recently. trying to determine how to store certain values. The best wide spread example I can think of is Federal Employee Identification Number - more commonly knows as tax id or social security for companies.

For various business / tax / legal reasons we need to store this number for some of our clients. Now this is a numeric 9 digit field . However it doesn't change. There are no calculations to be done with it, this is an external reference field that we have no control over. So should it be stored as char(9) or int(9) ? What are the benefits of storing one way or another? What's the most efficient way: in terms of space usage(I'm assuming int) , and in terms of performance ( speed of look ups) assuming that we will create a unique index on this column either way?

For the purpose of this exercise DB doesn't matter, but if want to elaborate I use MariaDB cluster ( MySQL)

  • An initial thought, without knowing the range or rules for "Federal employee id number": If it can have leading zeroes I guess int would not be the best choice :) – Mackan Mar 5 '15 at 7:45
  • That's why I used FEIN as an example, as it is a very common number. Like Social Security for people - thought didn't need to explain it. But essentially it's a 9 digit number. First 2 digits do have significance I think in terms of to which IRS center this business reports to, but I don't think this matters in our case - as we are just treating it as a 9 digit number or string - whatever the case might be. – konung Mar 5 '15 at 15:58
  • 1
    You didn't - I just pointed out that if it can have leading zeroes an INT would perhaps not be the best option (but looking at the answer below from @jynus perhaps I was mistaken. Didn't know about zerofill). – Mackan Mar 5 '15 at 18:28
1

Before start, please note that external keys are usually bad as primary keys, because they are larger than needed, they can change and some people may not have one, or have duplicated ones (even if legally that shouldn't happen).

Speaking correctly, the EIN is a code of 9 digits. As such, the technically correct value should be a string. In particular, for MySQL (yes, the database and storage engine matters a lot, although not so much between MySQL or MariaDB), that should be (I'm assuming it is a not null field) a:

CHAR(9) charset ascii NOT NULL

That should only take 9 bytes of fixed space in most engines. Please remember to use ascii, and not other charset, as you may waste 3 or 4 bytes per character.

The reason to check if logically a number should be an integer or a string is checking if it has to be used in arithmetic operations and the ordering. If someone asks you to order the EINs, '000-00-0009' should be before '100-00-0000', so zeros are important.

Having said that, using something like

int(9) UNSIGNED ZEROFILL NOT NULL

Would take you less size (4 bytes) and on the command line client it will show you the preceding zeros:

mysql> SELECT * FROM ein;
+-----------+
| ein       |
+-----------+
| 123456789 |
| 000000001 |
+-----------+

One common misunderstanding of int(NUMBER), is that we are telling the integer that it can only be up to 9 digits. That is incorrect. An int will always take 4 bytes and, in the case of an unsigned int, it will take values from 0 to 4,294,967,295. Most applications will ignore the size anyway.

Less space means less amount of bytes read and written to disk, and more free space on memory. And even if 4 to 9 bytes may not be a lot, if you have 200 million records, that is 1GB less of information (more if you have into account indexes). If you do not have a large amount of records, that may not affect you so much, although depending on the operations performed (it may involve transforming it into a character), both approaches may have its downsides.

There is a last option, probably way worse in performance, but that could help with consistency. MySQL has the "NUMERIC/DECIMAL" datatype, so you could also define it as:

ein decimal(9,0) zerofill unsigned not null

That should only take 4 bytes in MySQL, but it disallows both non-number characters (something that char allows) and numbers > 999,999,999 and <0 (unlike int), so it may be a bit less error-prone, but usually fixed-point arithmetic takes more cpu cycles than simple integers.

In general, performance issues can only be checked once you have the whole system running– sometimes trying to over-optimize is a mistake if there are not so many records and it makes working with them more complicated. Stick with the easiest one at first and optimize later.

  • Thank you jynus. To clarify: when I said it's an external key - I didn't mean I'm using it as a primary key anywhere in my db, just as a conviniet lookup key/value that is unique for each customer. Thank you for elaborating on storage issues. However on performance, generaly speaking, which indexed column will be faster: int, char or decimal ( very clever suggestion by the way) – konung Mar 5 '15 at 15:53
  • Also I wouldn't worry about zerofill - as in our case we handle numbers like that ( part numbers) on an app level. I don't think presentation logic belongs in DB. Plus nobody has direct access to DB – konung Mar 5 '15 at 15:54
  • Also could you elaborate on how this would be different for let's say PostgreSQL or even nosql db like Mongo or Couch? – konung Mar 5 '15 at 15:55
  • 1
    There is no tl;tr- I mentioned performance when I said "Less space means less amount of bytes read and written to disk, and more free space on memory. And even if 4 to 9 bytes may not be a lot, if you have 200 million records, that is 1GB less of information (more if you have into account indexes)." Using a numeric value uses less space, so it is generally preferred, but if that is going to have an actual impact on performance will depend on the number of rows, resources available and load- cannot be said for sure without testing. – jynus Mar 5 '15 at 17:01
  • InnoDB or MyISAM, as also does Postgres and Mongo, store data and indexes in different physical formats, sometimes optimizing for reads, sometimes for writes, sometimes for space. Also the indexing algorithms may be different. In all cases, less amount of data is usually preferred, but the gains will vary a lot. Asking a very general question as such is not very useful. Test first by yourself and if you see strange results, ask to see if you did something wrong or someone else can give you hints regarding configuration. – jynus Mar 5 '15 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.