10

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.

Victor
  • 13,010
  • 18
  • 83
  • 146

5 Answers5

9

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.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • That's a good idea. Does this method solve the problems Oli Charlesworth mentioned? – Victor Nov 09 '11 at 15:07
  • The space/access-speed benefits are assuming that the data isn't padded to word-size for alignment purposes. Human-readability is also solved with an enum (which gives you validation for free). – Oliver Charlesworth Nov 09 '11 at 15:31
  • 1
    a) If data is padded to word size, then char(1) will still be faster than a join, because although it's now the same size as an integer, it won't require a join. b) Enum isn't SQL. Different platforms support it in different and incompatible ways. None of the mainstream commercial dbms support it at all, AFAIK. MySQL and PostgreSQL support it in different and incompatible ways. c) Changes to enumeration require altering the schema; changes to a table related by a foreign key reference requires only inserting a row. (So validation isn't quite free.) – Mike Sherrill 'Cat Recall' Nov 09 '11 at 16:17
  • A join with what? The OP isn't talking about joins, AFAICS. – Oliver Charlesworth Nov 09 '11 at 21:13
  • 1
    A join with whatever table tells users that 1 means 'draft'. – Mike Sherrill 'Cat Recall' Nov 09 '11 at 21:40
  • I can't decide which is best. Yours and Cli Charlesworth's both are good. I'll vote your answer up. – Victor Nov 11 '11 at 14:22
  • Sizing of char|digit will also affect, Int will always be best to go for perfomance https://stackoverflow.com/a/2346973 – Ravi Parekh Aug 12 '20 at 08:41
4

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.

Oliver Charlesworth
  • 267,707
  • 33
  • 569
  • 680
2

I think the option you choose should depend on how well the tools/frameworks you use work with each feature.

Many database/ORMs deal poorly with enums, requiring custom code (don't understand the concept of "enumerated type").

That said... probably I'd use strings.

Strings:

  • use more space but in your case names are short and you can easily read a data-dump without the enum-table legend. Nowadays, for a blog / CMS, storage is hardly a issue
  • performance differences are usually small
  • you cannot easily rearrange the members of enum-tables (you've to force the "original" integer values).

Strings are also the choice of some well known CMSs (e.g. Drupal 7).

Of course this is a late answer but it could be useful to other readers.

manlio
  • 18,345
  • 14
  • 76
  • 126
0

Storing data in the integer form is always more reliable than the character or string.

Create two tables such as blog_status and blog_details

In the blog_status maintain the master status of blog like you said draft, pending and publish Table structure of blog_status 

Create table blog_status
(
blogstatus_id int,
blogstatus_desc varchar(10),
primary key(blogstatus_id)
)

And then create another table where you want to use the blog_status in this way, you can always improve reuse able and performance of your application

Create table blog_details
(
  blog_id int,
  blog_title varchar(10),
  blog_postingdate datetime,
  blog_postbox varchar(max),
  blog_status int, ---------------------> This should be your blogstatus_id value
  primary key(blog_id)
)

There is no point of use the x^2 expression or formula. I hope, I have clear your doubt . If you find the answer helpful please mark it as your answer else let me know...

AnandMohanAwasthi
  • 839
  • 7
  • 19
0

The database theorist in me thinks that you shouldn't use lookup tables for single column attributes because it leads to unnecessary splitting of your data; in other words, you don't need to have a table with two columns (and ID value and an attribute name). However, the DBA in me thinks that for performance reasons, splitting your data is a very valid technique. Indexing, disk footprints, and updates become very easy when using lookups.

I'd probably split it.

Stuart Ainsworth
  • 12,792
  • 41
  • 46