Type to Use for "Status" Columns in a SQL Table

Type to use for Status columns in a sql table

Go with number 3. Create a view that join's in the status value if you want something inspectable.

what is best column type for status in MySQL

My opinion would be that if you are simply selecting the value then it would not matter whether you will use varchar or tinyint.

But if you are going to use it in some where condition then tinyint would have an edge over varchar. Also it is recommended to choose the smallest data type possible.

Varchar(10) or int for Status Column in SQL

As a best practice you should choose the narrowest column. The narrower the column, the less amount of data SQL Server has to store, and the faster SQL Server is able to read and write data. In addition, if any indexes are created on the column, index size will be narrower and hence the overall performance will be impacted on a positive side.
As you have column that is designed to hold only numbers, use a numeric data type, such as INTEGER, instead of a VARCHAR or CHAR data type. Numeric data types require less space to hold the same numeric value as does a character data type. This helps to reduce the size of the columns, and can boost performance when the columns is searched (WHERE clause), joined to another column, or sorted.

Also if you are going to store values in columns as numbers -1, 1 through 10, then the TINYINT data type is more appropriate that the INT data type.

Hope this helps!!

A column called Status in MySQL

Status can be its own column if you wrap it, in MySQL, with ``.

SELECT `t`.`Status`. FROM `t`

But for the sake of avoiding confusion later on, it may be better for you to distinguish it in some other way.

phpmysql - What data type to store time & status in 1 column using mysql?

So what I understand is, you want to change from storing them in separate tables, to one column in one table. Use VARCHAR. VARCHAR is meant to store all kinds of characters, not just letters. You cannot store letters in TIME or FLOAT column data types so if you must store them as one column in one table those are not options.

DB Table Design - Different Statuses for Mail Type

This might be an opinion based but..

You should not store different data in same column. You should split all those incoming/outgoing columns into two and fill only relevant ones for different type of mail

In your solution, if you have John in Recipient/Sender column, you don't know whatever John is recipient or sender until you check the PostType column and that's not really good design. Each column should be self-identifying.

I would suggest changing your design to:

ID
Sender 
Recipient
DispatchDate
ReceiveDate
IncomingStatus
OutgoingStatus
PostType

And if you really need combined data to show somewhere, you can create a view for that purpose as:

SELECT 
   CASE WHEN PostType = 1 THEN Sender ELSE Recipient END AS [Recipient/Sender] 
   CASE WHEN PostType = 1 THEN DispatchDate ELSE ReceiveDate END AS [Dispatch/Receive Date]

etc..

(or add them as computed columns)

Also by separating columns, you make yourself a room to maybe also store recipients for outgoing mails for example, if that need arise in the future.

---

Related Topics