Anatomy of a CREATE TABLE
A good CREATE TABLE defines columns with precise types, a clear primary key, foreign keys with their cascade actions, indexes on filtered or sorted columns, and constraints that protect integrity. What looks like overhead up front pays off in reliability later.
Primary key: BIGSERIAL vs UUID
For internal tables that grow fast, BIGSERIAL (Postgres) or BIGINT IDENTITY (SQL Server) remain the most efficient: 8 bytes, sortable. For IDs visible to users or shared between services, UUID (v7 if you want time ordering) avoids leaking cardinality and lets you mint IDs without coordination.
Timestamp types
Use TIMESTAMPTZ in Postgres (timestamp with time zone): stored in UTC,
converted to the client TZ. Plain TIMESTAMP without TZ is a trap: it seems
to work until you cross time zones. In MySQL, use DATETIME with the rule
"store UTC in the app, format on the client".
NOT NULL and defaults
Every column allowed to be NULL should be a deliberate choice. If a field always has a
value, mark it NOT NULL from day one. created_at TIMESTAMPTZ NOT NULL DEFAULT
NOW() is the default config for any new table.
CHECK constraints
CHECK (price >= 0), CHECK (status IN ('pending', 'paid')),
CHECK (start_date < end_date). What your app should validate, validate it
in the database too. It's the last line of defense against corrupt data.
Foreign keys: CASCADE, RESTRICT, SET NULL
ON DELETE CASCADE deletes children when the parent is deleted; useful for
composition (order → items). RESTRICT forbids deletion if children
exist; the most conservative. SET NULL orphans the child; useful for
authors and posts when you want to keep the post.
Indexes: when and which
Index columns used in WHERE, JOIN, ORDER BY. For equality and ranges, B-tree (default).
For full-text search, GIN in Postgres. For arrays and JSON, GIN as well. Too many indexes
slow INSERTs and take space: monitor with pg_stat_user_indexes which ones
aren't used and drop them.
Soft delete
If you'll "delete" records but want to keep them, add a nullable deleted_at
TIMESTAMPTZ. Reads filter WHERE deleted_at IS NULL. Trade-off: every
query needs that filter and unique indexes need partial conditions.