Difference between storing integer or string in database table

Question

I'm concern about performance, engineering and readability. Let's say I have a blog, and every post has its status: published (4), pending review (2), draft (1). What is the recommended to store these information in the status column?

status        <======= storing status as string
========
pending
published
draft

status        <======= storing status as integer
========
2
4
1

Also, if we should store integer, should we refrain from storing running integer: 1, 2, 3, 4, 5, as opposed to storing a ^2 integer: 2, 4, 8, 16, 32?

Many thanks.

Answer 1

I think your best bet for faster performance, less storage space, and readability is to use CHAR(1)--(p)ublished, pending (r)eview, and (d)raft. You can validate that data with either a CHECK constraint or a foreign key reference.

CHAR(1) takes substantially less space than an integer. It's directly readable by humans, so it doesn't need a join to understand it. Since it's both smaller and immediately readable, you'll get faster retrieval than a join on an integer even on a table of tens of millions of rows.

Answer 2

Storing as a string:

• wastes space
• takes longer to read/write
• is more difficult to index/search
• makes it more difficult to guarantee validity (there's nothing to prevent someone inserting arbitrary strings)

Ideally, you should use an enum type for this sort of thing, if your database supports it.