Ni zipi data types za kawaida za SQL na unazichaguaje?

Question

Accepted Answer

Kila safu ina **data type** inayofafanua kile inachohifadhi — namba, maandishi, tarehe, booleans, n.k. Kuchagua type sahihi ni muhimu kwa **ufanisi wa uhifadhi, usahihi, na utendaji**. Types hutofautiana kiasi kwa hifadhidata, lakini makundi ni ya kawaida.

## Numeric types

```sql
INT / INTEGER          -- whole numbers (4 bytes)
BIGINT                 -- large whole numbers (8 bytes) — for IDs that grow large
SMALLINT               -- small whole numbers (save space)
DECIMAL(10,2) / NUMERIC -- EXACT decimal — use for MONEY (no rounding errors)
FLOAT / REAL / DOUBLE  -- approximate floating point — NOT for money
SERIAL / AUTO_INCREMENT -- auto-incrementing integer (for primary keys)
```

**Muhimu:** tumia **`DECIMAL`/`NUMERIC` kwa fedha** (hesabu kamili), kamwe `FLOAT`/`DOUBLE` (zilizo na makosa ya kuzungusha yanayoharibu hesabu za kifedha).

## Text types

```sql
VARCHAR(n)    -- variable-length text up to n chars (the common choice)
CHAR(n)       -- FIXED-length (padded) — for codes of known length (e.g. country codes)
TEXT          -- large/unlimited text (articles, descriptions)
```

`VARCHAR(n)` ni type ya kila siku ya maandishi; `TEXT` kwa maudhui makubwa; `CHAR(n)` kwa kodi za urefu thabiti.

## Tarehe/saa, boolean, na nyingine

```sql
DATE           -- date only (2024-01-15)
TIME           -- time only
TIMESTAMP      -- date + time (with or WITHOUT time zone — TIMESTAMPTZ in Postgres)
BOOLEAN        -- true/false
UUID           -- universally unique identifier (good for distributed IDs)
JSON / JSONB   -- structured JSON data (JSONB in Postgres is indexed/efficient)
BLOB / BYTEA   -- binary data
```

## Kwa nini kuchagua type sahihi ni muhimu

```text
✓ Storage — smaller types use less space (INT vs BIGINT; VARCHAR vs TEXT)
✓ Correctness — DECIMAL for money (exact), proper date types for dates, BOOLEAN for flags
✓ Performance — correct types index and compare efficiently; right size = less I/O
✓ Validation — the type itself constrains what can be stored (an INT can't hold text)
✓ Time zones — use TIMESTAMPTZ for timestamps to avoid time-zone bugs
```

## Kwa nini ni muhimu

Kuchagua data types zinazofaa ni kipengele cha msingi cha muundo wa hifadhidata kinachoathiri **uhifadhi, usahihi, na utendaji**, hivyo kuelewa types za kawaida na jinsi ya kuzichagua ni maarifa muhimu ya kila siku.

Kujua makundi — numeric (INT, BIGINT, DECIMAL, FLOAT), text (VARCHAR, CHAR, TEXT), tarehe/saa (DATE, TIMESTAMP), boolean, na types maalum (UUID, JSON/JSONB) — ni la lazima kwa kubuni majedwali kwa usahihi.

Chaguo kadhaa zina madhara halisi yanayostahili kueleweka: kutumia **`DECIMAL`/`NUMERIC` kwa fedha** badala ya `FLOAT` (suala muhimu la usahihi — makosa ya kuzungusha ya floating-point huharibu hesabu za kifedha, mdudu wa kawaida, mzito), kuchagua **ukubwa sahihi wa integer** (`BIGINT` kwa IDs zinazoweza kukua zaidi ya wigo wa INT — wasiwasi halisi kwa majedwali makubwa), kutumia **types sahihi za tarehe/saa** (hasa `TIMESTAMPTZ` inayojua timezone kuepuka mdudu wa timezone), na kuchagua text types zenye ukubwa unaofaa (VARCHAR dhidi ya TEXT).

Chaguo sahihi la type huathiri ufanisi wa uhifadhi (types ndogo hutumia nafasi na I/O kidogo), usahihi (type huthibitisha na kuwakilisha data kwa usahihi), na utendaji (types sahihi huingiza index na kulinganisha kwa ufanisi).

Kwa kuwa kila safu inahitaji chaguo la type, na kwa kuwa chaguo mbaya husababisha matatizo halisi (FLOAT kwa fedha kuharibu hesabu, kufurika kwa INT kwa IDs, mdudu wa timezone), kuelewa data types za kawaida za SQL na kanuni za kuzichagua — usahihi kwa fedha, ukubwa unaofaa, timestamps zinazojua timezone — ni maarifa muhimu, ya msingi kwa muundo bora wa hifadhidata unaozuia darasa la matatizo madogo lakini makubwa ya usahihi na utendaji wa data.