The difference between storing an integer or row in a database table

Question

I worry about performance, development, and readability. Let's say I have a blog, and each post has its own status: published (4), pending review (2), draft (1). What is recommended to store this information in the status column?

 status <======= storing status as string ======== pending published draft status <======= storing status as integer ======== 2 4 1 

In addition, if we must store an integer, we must refrain from storing an executable integer: 1, 2, 3, 4, 5 , as opposed to storing an integer ^ 2: 2, 4, 8, 16, 32 ?

Thank you very much.

Answer 1

I think your best bet for faster work, less storage and readability is to use CHAR (1) - (p) published, expecting (r) eview and (d) raft. You can verify this data using a CHECK constraint or a foreign key reference.

CHAR (1) takes up significantly less space than an integer. It is directly readable by people, so a connection is not required to understand it. Since it is both smaller and immediately readable, you will get a faster search than combining an integer even in a table of tens of millions of rows.

Answer 2

Saving as a string:

• space for waste
• it takes more time to read / write
• harder to index / search
• complicates the guarantee of reality (nothing prevents the insertion of arbitrary lines)

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

Answer 3

I think that the option you choose should depend on how well-used tools / frameworks work with each function.

Many databases / ORMs do not work well with enumerations that require custom code (they do not understand the concept of an "enumerated type").

That says ... maybe I would use strings.

Strings:

• use more space, but in your cases the names are short and you can easily read the data dump without the legend of the enum table. Blogging / CMS is currently unlikely to be a problem.
• performance differences are usually small
• you cannot easily change the elements of enumerated tables (you must force the "original" integer values).

Strings are also a selection of some well-known CMS (e.g. Drupal 7).

---

^{Of course, this is a late answer, but it may be useful to other readers.}

Answer 4

Saving data in integer form is always more reliable than a character or string.

Create two tables such as blog_status and blog_details

In blog_status, maintain the blog’s master status, as you said, a project that is awaiting and publishing the blog_status table structure

 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 blog_status in this way, you can always improve the reusability 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) ) 

It makes no sense to use the expression x ^ 2 or the formula. I hope I clarified your doubts. If you find the answer helpful, please mark it as your answer, let me know ...

Answer 5

The database theorist in me thinks that you should not use lookup tables for attributes of a single column, because this leads to unnecessary splitting of your data; in other words, you do not need to have a table with two columns (and an ID value and an attribute name). However, the DBA in me thinks that for performance reasons, sharing your data is a very efficient method. Indexing, disk footprints, and updates become very easy when using search queries.

I would split him.