message table schema - body TEXT or varchar [closed]

Question

I want to make a small social network. For messaging body which one is more suitable? VARCHAR or TEXT? if VARCHAR how long is efficient?

Answer 1

To completely determine column type, I might suggest to do following:

1. Check possible data types in real world: is it string, or number, or large text block, or date?
2. Determine which operation types you would like to perform with the column: for example, barcode has numeric format, but calculate sum or average of barcode isn't valid.

You have selection of two types: TEXT or VARCHAR. If you would like to perform FULLTEXT search, then you have to use TEXT. For other cases VARCHAR acts almost same as TEXT.

"Which of them to use?" depends on subject area and is primarily opinion-based question. If you ask me, then I might suggest to use VARCHAR (if no fulltext search is planned) up to VARCHAR(2000).

---

From MySQL.com:

In the following table, M represents the declared column length in characters for nonbinary string types and bytes for binary string types. L represents the actual length in bytes of a given string value.

VARCHAR(M): L + 1 bytes if column values require 0 – 255 bytes, L + 2 bytes if values may require more than 255 bytes.

TEXT: L + 2 bytes, where L < 2^16.

For small messages, storage space of which requires less than 255 bytes, VARCHAR will save some extra disk space.

---

I'm supposing that you use MySQL RDBMS.