1

I was listening to some people at work speak about a database column. Essentially, we wanted to add a new column which serves as an FK to a lookup table. It's basically your preferred contact method (primary phone or primary e-mail). So main table 'User' has an FK to 'PreferredContactMethod'

Person # 1: "Let's store the FK column as an unsigned tiny int, and make the PK lookup table simply have a tinyint PK and a text description/code"

Person # 2 "It's irreleant whether we store the datatype as unsigned tinyint, or char(x) in terms of space in MySQL, so why make you have to do joins to find out what the value is for the lookup column? Instead, make the FK a char(x), and make the PK char(x) on the actual table"

Person # 3 "No it's not a good idea to make a PK represented as characters. It's not efficient. Handling something like unsigned tinyint is better than text. And since there are only two values, why don't we just store it as a single column (not an FK) with a value of either 0, or 1. This way it's more efficient and you don't have to join anything."

So after listening to this all, I started wondering who is right. My suspicion is this is so trivial that it wouldn't matter in terms of performance, but i'm so curious now as to what the pros and cons are that I'd love someone's take on this.

Thank you for your time.

NullHypothesis
  • 4,286
  • 6
  • 37
  • 79
  • Do what is simplest. Then show us the `SHOW CREATE TABLEs`. We will probably say "that is the best". (I think your co-workers are over-thinking it.) – Rick James Mar 29 '17 at 01:38

1 Answers1

1

It's typical for questions like this to have no right or wrong answer. Or actually, either answer can be right, because it depends on how you're going to use the data.

A good case for storing an int/tinyint to the lookup table is that the values change regularly, and you want to allow changes to happen in the lookup table without changing all the rows that reference it.

It's a good thing to store the PK as a string if you have a relatively small lookup table that doesn't change frequently, and the strings are fairly short. If the strings are long, this could make the FK reference bulkier that necessary and use a lot of space.

The inefficiency of storing a PK as a string doesn't affect a lookup table very much. That table is pretty small. The cost of a string PK is mostly due to frequent random inserts. But this doesn't impact a lookup table.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • thank you! Does it matter if the column is varchar(25) vs char(25) in terms of efficiency/storage? That came up as well. – NullHypothesis Mar 25 '17 at 02:26
  • Yes, a char(25) stores 25 characters for every row, even if many of the values are shorter than 25 characters. Varchar(25) stores more compactly, only as many characters as needed for each string. So if you typically have strings less than 25 characters, use varchar instead of char. – Bill Karwin Mar 25 '17 at 18:39
  • One reason to use char(25) even if it is less efficient is that in MyISAM, making every row of equal length grants some efficiency when searching. But in InnoDB, rows are stored differently and there's no advantage to using fixed-length rows. – Bill Karwin Mar 25 '17 at 18:41
  • ok thank you Bill. So InnoDB, use varchar(25) as my PK, and MyISAM, use char(25). Our table will have a total of 2 rows in it (for now) and the values are 'PHONE' and 'EMAIL'. Maybe we see a few more rows get added, but definitely less than 10. – NullHypothesis Mar 25 '17 at 19:05
  • 1
    Right, but don't use MyISAM. It's becoming deprecated, it doesn't support foreign keys anyway, and there are [other problems with MyISAM](http://stackoverflow.com/a/17706717/20860). – Bill Karwin Mar 25 '17 at 21:01
  • ugh - my database appears to be MyISAM. Do I want to change this? Is it even possible? – NullHypothesis Mar 26 '17 at 19:14
  • Probably, but you should test before making a final decision. The only downside I see is that InnoDB tends to take more space to store equivalent data. As for how to do it, there are numerous other answers on SO: http://stackoverflow.com/search?q=convert+myisam+to+innodb – Bill Karwin Mar 27 '17 at 02:05
  • THANKS! Will probably do this later this year – NullHypothesis Mar 27 '17 at 19:17
  • @BillKarwin - I don't even agree with the argument for `FIXED` MyISAM tables -- the wasted disk space leads to extra I/O, which _may_ slow down the application more than `FIXED` speeds it up. – Rick James Mar 29 '17 at 01:35
  • @RickJames, you aren't going to get me to defend MyISAM! :-) – Bill Karwin Mar 29 '17 at 03:44