CODEWITHSUNDEEP
mysqldatabasedatabase-designrelational-databaseinnodb
NullHypothesis
NullHypothesis

Reputation: 4506

MySQL - "Lookup" column data type - ideal way to store simple lookups

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.

Upvotes: 1

Views: 605

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562230

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.

Upvotes: 1

Related Questions