Data types, naming and good habits

Money: exact, always

Module 1 showed that 0.1 + 0.2 is 0.30000000000000004 in floating point. Here is why that matters in design. FLOAT, REAL and DOUBLE store numbers in binary, and most decimal fractions have no exact binary form, just as 1/3 has no exact decimal form. Small errors pile up and comparisons fail.

SELECT 0.1 + 0.2 = 0.3;          -- 0   (false!)
SELECT 0.1 + 0.2 + 0.3;          -- 0.6000000000000001
SELECT ROUND(2.675, 2);          -- 2.67, not 2.68: 2.675 is really 2.67499999...
SELECT (10 + 20) = 30;           -- 1   (paise as integers are exact)

• PostgreSQL, MySQL, SQL Server: use DECIMAL(12, 2) or NUMERIC(12, 2). The first number is the total digits, the second the digits after the point.
• SQLite (no true DECIMAL type) and many payment systems: store whole paise as INTEGER. ₹1,234.50 is 123450. Format it as rupees only when you show it.
• Store the currency too (a currency column holding 'INR') if the app may ever handle more than one.

Dates, times and time zones

A timestamp without a time zone is ambiguous: "2025-03-28 18:45" in Mumbai and in London are five and a half hours apart. The safe habit is to store every moment in UTC (Coordinated Universal Time) and convert to the user's zone only for display. India Standard Time is UTC+5:30, with no daylight saving.

• PostgreSQL: use TIMESTAMPTZ (timestamp with time zone). It stores the moment in UTC and shows it in the session's time zone.
• MySQL: TIMESTAMP is converted to UTC for storage; DATETIME is stored as typed, with no zone.
• SQLite: store UTC text 'YYYY-MM-DD HH:MM:SS'. CURRENT_TIMESTAMP and datetime('now') already return UTC.
• A pure calendar date with no time (a date of birth, a train's journey date) is a DATE; do not turn it into a timestamp.

SELECT datetime('2025-03-28 18:45:00', '+5 hours', '+30 minutes');
-- 2025-03-29 00:15:00   (the same moment shown in IST: already the next day)

Text lengths

In PostgreSQL, TEXT and VARCHAR(n) perform the same; VARCHAR(n) is a rule, not a speed trick. Use a length limit only when the business has one: a 6-digit PIN code, a 10-character PAN, a 2-letter state code. SQLite ignores VARCHAR lengths, so use CHECK (length(pincode) = 6) when you need the rule there. Store codes with leading zeros, like PIN codes and phone numbers, as text, never as integers.

Allowed values: CHECK, enum or lookup table

Naming conventions

• Use snake_case: order_items, created_at. Unquoted names in PostgreSQL are folded to lower case, so OrderDate silently becomes orderdate.
• Plural (customers) or singular (customer) table names: both are common. Pick one rule and use it everywhere.
• Name foreign keys after the table they point to: customer_id points to customers.id.
• Avoid reserved words (order, user, group) as names; they need quoting forever.
• Units in names: amount_paise, duration_seconds, weight_grams, so nobody guesses.

Audit columns and soft deletes

Add created_at and updated_at to most tables; they answer "when did this change?" during every support call. created_at can use DEFAULT CURRENT_TIMESTAMP. updated_at must be set on each update: MySQL has ON UPDATE CURRENT_TIMESTAMP, while PostgreSQL and SQLite need the app or a trigger (module 11).

A soft delete marks a row as deleted (deleted_at = the time) instead of removing it, so it can be restored and audited. The cost: every query must add WHERE deleted_at IS NULL, and UNIQUE rules must ignore deleted rows (a partial index, module 10). For personal data, privacy law may require a real delete, so check the rules.

Integer or UUID keys?

Integer ids are small, fast and readable, but they reveal how many rows you have and must come from one database. A UUID is a 128-bit random-looking value like 3f1c9a2e-... that any server can create without asking the database, which helps with offline apps and merging data. It is larger and, if fully random (version 4), scatters index inserts. Time-ordered UUIDs (version 7) reduce that; PostgreSQL has a uuid type and gen_random_uuid(), and PostgreSQL 18 added uuidv7(). Many teams use an integer key inside and a UUID in public URLs.

Common mistakes

• FLOAT for money. Fix: DECIMAL or integer paise.
• Storing local times without a zone. Fix: store UTC; convert on display.
• Phone numbers or PIN codes as INTEGER, losing leading zeros. Fix: TEXT with a CHECK on length.
• Mixed naming: OrderDate, order_date and orderdate in one schema. Fix: write a one-page naming guide.
• Soft-delete columns that queries forget to filter. Fix: a view that hides deleted rows (module 10).