Wadanne jerin nau'in bayanan SQL da kuma yaya za ka zaɓi su?

Question

IT Interview Practice · Accepted Answer

Kowane ginshiki yana da **nau'in bayanan** da ke bayyana abin da yake ajiye — lambobi, rubutu, kwanakin, boolean, da sauransu. Zaɓin nau'in daidai yana da mahimmanci ga **ingantaccen ajiyewa, daidaito, da ƙarfin aiki**. Nau'ina suna ban ɗa ɗan gari da kowa-kowa wajen bayanan bayanan, amma kategoroyin suna daidai.

## Nau'ina lamba

```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)
```

**Mahimmanci:** yi amfani da **`DECIMAL`/`NUMERIC` don kuɗi** (daidaitaccen lissafi), kuma kar ka yi amfani da `FLOAT`/`DOUBLE` (wadanda suke da kuskure na juzu'u da suke lalata lissafin kuɗi).

## Nau'ina rubutu

```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)` shine nau'in rubutu na kullun; `TEXT` don babbar abun; `CHAR(n)` don lambobi masu tsayayye.

## Kwanakin/lokaci, boolean, da sauransu

```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
```

## Dalilin zaɓin nau'in daidai

```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
```

## Dalilin da yake dakaita

Zaɓin nau'ina bayanan daidai shine tushen fasalin bayanan bayanan da ke shafar **ajiyewa, daidaito, da ƙarfin aiki**, saboda haka fahimtar jerin nau'ina da yaya za ka zaɓi su shine ilmin kullun mahimmanci.

Sanin kategoroyin - lamba (INT, BIGINT, DECIMAL, FLOAT), rubutu (VARCHAR, CHAR, TEXT), kwanakin/lokaci (DATE, TIMESTAMP), boolean, da nau'ina musamman (UUID, JSON/JSONB) - shine mahimmanci ne don tsara tebur daidai.

Zaɓuwa daban-daban sune da abubuwa masu tasiri na gidi wadanda fahimta suke mahimmanci: amfani da **`DECIMAL`/`NUMERIC` don kuɗi** maimakon `FLOAT` (muhimmiyar bata-taswira - kuskure na juzu'u na floating-point suke lalata lissafin kuɗi, classic, muhimmiyar kuskure), zaɓin daidai **girman lamba** (`BIGINT` don IDs wanda na iya zurawa fiye da kewayon INT - gajiya gida don manyan tebur), amfani da nau'ina kwanakin/lokaci masu kyau (musamman yawan kakaisa-jahannama `TIMESTAMPTZ` don guje wa kuskurera na waje), da zaɓin nau'ina rubutu masu dace (VARCHAR dan TEXT).

Zaɓin nau'in daidai yana shafar ingantaccen ajiyewa (nau'ina ƙanana suna amfani da wuya kasa da I/O), daidaito (nau'in yana tantance kuma yamutane gabatarwa bayanan), da ƙarfin aiki (nau'ina daidai suna faru da jiya da ƙarfafa da mai sauƙi).

Tun da kowane ginshiki yana buƙatar zaɓin nau'i, kuma tun da zaɓuwa marasa kyau sune haifar da matsaloli (FLOAT don kuɗi lalata lissafi, INT kowaici don IDs, kuskurera na waje), fahimtar jerin nau'ina bayanan SQL da ƙa'idodin zaɓin su - adikita-waje don kuɗi, dace-dace girma, yawan kakaisa-jahannama timestamps - yana mahimmanci, ilmin tushe don kyakkyawar fasali bayanan da ke tare da matakai masu ɗaɗa amma muhimmiyar daidaito bayanan da matsaloli na ƙarfin aiki.

Nau'ina lamba

Nau'ina rubutu

Kwanakin/lokaci, boolean, da sauransu

Dalilin zaɓin nau'in daidai

Dalilin da yake dakaita

Tambayoyi masu alaƙa