📇Tables, rows, and columns
Tables are not spreadsheets
A table is a persisted multiset of rows that share the same columns. Row order is not part of the relational model unless you add ORDER BY. A column is a named, typed attribute, nullable unless you say otherwise with NOT NULL.
🧮Map SQL words to algebra words
Books say relation / tuple / attribute; databases say table / row / column, the idea is the same.
| SQL term | Algebra term | What to remember |
|---|---|---|
| Table | Relation | Named bag of rows; duplicates allowed unless constrained by keys/UNIQUE |
| Row | Tuple | One fact instance, e.g., one plate, one assay reading for a well |
| Column | Attribute | Single field with a type; determines comparison and aggregation behavior |
Implicit grain: one row means two things
Storing “patient + visit + assay” in one wide row without a key that matches how you query invites duplicate imports and impossible updates (“which assay does this column belong to?”).
Split tables or add a natural key
Normalize into child tables keyed by the real entities, or document a composite primary key that matches the grain of every SELECT you run.
Peek at structure + sample rows
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'plates'
ORDER BY ordinal_position;| column_name | data_type | is_nullable |
|---|---|---|
| plate_id | integer | NO |
| barcode | character varying | NO |
| wells | smallint | YES |
Concrete rows from the table
SELECT plate_id, barcode, wells
FROM plates
ORDER BY plate_id
LIMIT 3;| plate_id | barcode | wells |
|---|---|---|
| 1 | PLT-0001 | 96 |
| 2 | PLT-0002 | 384 |
| 3 | PLT-0003 | NULL |
Naming discipline
Columns named value, data, or flag age poorly, prefer od600, concentration_ng_ul, is_qc_passed so joins read like sentences in six months.
TL;DR
A table stores tuples (rows) of attributes (columns) with a fixed header. Each row should represent one entity at the table's grain (e.g. one measurement event). Good naming + keys make joins readable; vague columns make SQL unmaintainable even when the engine is fast.