One-to-one means each row in Table A maps to exactly one row in Table B and vice versa. One-to-many means a row in Table A can map to multiple rows in Table B, but each row in Table B maps back to only one row in Table A. Choose one-to-one when you have optional data that only some rows need (like billing addresses), when the data is large and most rows do not need it, or when you want to split a wide table for organizational reasons. Choose one-to-many for standard parent-child relationships like customers and their orders. One-to-one adds join complexity when you always query the data together, so merge tables if they are always queried jointly.

The 47-column table with repeated supplier fields is a denormalized design that causes update anomalies, storage waste, and data inconsistency. When a supplier changes their address, you must update every product row that references that supplier. Missing updates create inconsistent data. The fix is normalization: extract suppliers into their own table with a surrogate key, then replace the denormalized supplier fields in the products table with a foreign key reference. This eliminates redundancy, prevents update anomalies, and ensures address changes happen in one place. The risk is that 47-column tables often have other problems lurking, so a full schema review is advisable.

Always use a junction table for many-to-many relationships. Storing comma-separated IDs or JSON arrays violates first normal form (atomic values), prevents efficient joins, makes indexing impossible on the stored relationships, and complicates updates. A junction table with foreign keys to both tables enables proper joins, enforces referential integrity, allows indexing on either side of the relationship, and accurately models the true cardinality. The only case where denormalized storage might make sense is when the relationship is truly optional and almost never queried, but even then the tradeoff is usually not worth it.

Embedding addresses in orders works when you always need the address with the order and the address never changes after the order is placed. It simplifies queries by eliminating joins and performs well for read-heavy order reporting. The separate addresses table is correct when addresses are shared across multiple entities (customers, suppliers), when addresses can be updated and those updates should propagate, or when you need to query "all orders shipped to California" across all customers. Embedding creates redundancy and update anomaly risk. The right choice depends on whether the address is an attribute of the order or a shared entity that happens to be referenced by the order.

Use VARCHAR(2) or VARCHAR(3) for country codes when you only need to store and display the code without additional context. Use a lookup table when you need to store additional data about each country (name, region, population, calling code) or when you want the database to enforce referential integrity preventing invalid codes. VARCHAR(255) for a two-letter country code wastes storage and bypasses the ability to catch typos at the database level. A countries table with a foreign key is the right choice when the country is a domain entity you care about beyond just the code.

Audit fields track when records were created and modified. created_at should default to CURRENT_TIMESTAMP and never change. updated_at should also default to CURRENT_TIMESTAMP but update on every row change using a trigger or application-level logic. deleted_at supports soft deletes, allowing records to appear deleted without physical removal. Implementation choices: application-level (your ORM or code sets the values), database triggers (guaranteed regardless of access path), or generated columns (in PostgreSQL, GENERATED ALWAYS AS). The key decision is whether you need the database to enforce these automatically or whether application-level implementation is sufficient. Database-level enforcement is more robust since it covers direct SQL access.

Excessive NULLs often indicate a schema that has merged two entities that should be separate. NULL means "unknown" or "not applicable," but if 80% of rows have a value in one column and only 20% in another, those columns might belong in a separate table with a one-to-one relationship. NULLable foreign keys are appropriate when the relationship is genuinely optional (a user may or may not have a spouse). NULLable columns that represent different categories of the same entity suggest subtype modeling is needed. Review whether the table is trying to represent multiple concepts or whether normalization would clarify the model.

CASCADE DELETE can silently remove large numbers of rows when a parent record is deleted. If you delete a customer who has 50,000 orders, the database locks every related row during deletion, blocking other writes to those tables for seconds or minutes. On busy production systems this causes incidents. CASCADE DELETE is dangerous when the child tables have many rows or when the delete happens without application-level awareness. Safer alternatives: SET NULL or SET DEFAULT (requires nullable FK), soft delete (set deleted_at instead), or batched hard deletes that chunk the deletion and let the system breathe between batches. Always test CASCADE behavior with production-scale data before deploying.

Big table schema changes require a multi-phase approach to avoid locking. Add new columns as nullable with no defaults first, then backfill in batches (UPDATE LIMIT 1000 with sleep between batches to reduce load), then add NOT NULL constraints once backfill is complete, then add defaults. For adding NOT NULL with a default on PostgreSQL 11+, the default is stored in the catalog and applied on read, so it does not require a table rewrite. For older versions or more complex changes, use the expand-contract pattern: add new column, migrate data, deprecate old column, drop old column. Never alter a multi-billion row table in a single statement in production.

Hard delete permanently removes the row from the database. Soft delete sets a deleted_at timestamp or is_active flag, keeping the row in the table but invisible to normal queries. Hard delete is appropriate when the data has no regulatory retention requirement, when deleted data serves no analytical purpose, and when storage is a concern. Soft delete is appropriate when you need an audit trail, when deleted records are referenced by other tables that must remain consistent, when regulatory requirements mandate data retention, or when you need the ability to restore accidentally deleted records. Soft delete complicates queries (every query must filter deleted rows) so only use it when the benefit justifies the overhead.

CHECK constraints validate that column values meet a condition before allowing insert or update. They enforce business logic at the database level that would otherwise require application-layer validation. Use CHECK when you need to validate domain-specific rules like price >= 0, status IN ('active', 'inactive'), or percentage BETWEEN 0 AND 100. Unlike NOT NULL, CHECK can validate complex conditions across multiple columns. Unlike triggers, CHECK is declarative and more performant. The tradeoff is that complex CHECK constraints can be harder to maintain than application validation, and some teams prefer keeping business logic in the application layer for testability.

SERIAL and BIGSERIAL are auto-incrementing integers suitable for single-instance databases with moderate row counts. BIGSERIAL handles values beyond 2 billion. UUIDs are 16-byte identifiers suitable for distributed systems where multiple databases might generate IDs independently. Choose SERIAL for small to medium tables where you never need to expose the ID externally. Choose BIGSERIAL for high-volume tables that might exceed 2 billion rows. Choose UUID when you need distributed ID generation, when IDs cross database boundaries, or when you want to obscure the count of records. UUIDs have storage and index performance costs so only use them when the distributed generation benefit justifies the overhead.

PRIMARY KEY enforces uniqueness, cannot be NULL, and creates a clustered index by default (except PostgreSQL). A table can only have one PRIMARY KEY. UNIQUE enforces uniqueness but allows NULL (multiple NULLs depending on database), and creates a non-clustered index by default. A table can have multiple UNIQUE constraints. PRIMARY KEY is appropriate for the row identifier. UNIQUE is appropriate for columns that should be unique but are not the row identifier, like an email address or badge number. The practical difference is that PRIMARY KEY implies NOT NULL and is indexed differently.

Three common approaches: Adjacency list (parent_id column referencing the same table) is simple and supports quick immediate parent lookups but requires recursive queries for tree traversals. Nested sets (lt/rgt columns) enable fast subtree queries but inserts are expensive since all right values must shift. Materialized path (storing full path like "/1/5/12/") enables prefix matching for subtree queries with reasonable inserts. For PostgreSQL, also consider ltree extension for hierarchical data. Choose adjacency list for simple hierarchies with frequent updates. Choose nested sets or materialized path for read-heavy trees where you frequently query entire subtrees.

JSONB stores semi-structured data as binary JSON, allowing flexible schemas without schema migrations. It suits data where fields vary per row or where the structure evolves rapidly. Normalized tables suit data with fixed schemas, where you need referential integrity, and where you query specific fields frequently. JSONB tradeoffs: you lose type safety at the column level, querying nested fields is slower than indexed column access, and you cannot enforce relationships with foreign keys. Normalization tradeoffs: schema changes require ALTER TABLE, and joins multiply for highly related data. Use JSONB for configuration, user preferences, or when the data structure genuinely varies. Use normalized tables for core business data with fixed schemas.

Estimate per-row storage by summing column sizes: INTEGER is 4 bytes, BIGINT is 8 bytes, VARCHAR(n) stores n + 1 to n + 4 bytes depending on content length, BOOLEAN is 1 byte, TIMESTAMP is 8 bytes, JSONB is variable but roughly content size plus overhead. Add row overhead (PostgreSQL row header is 23 bytes plus padding). Multiply by expected row count for table size, then multiply by 1.3 to 1.5 for index overhead. For columns with wide distributions, sample actual data sizes before committing to VARCHAR lengths. The goal is to catch storage mistakes before they become production problems.

Table names are singular (user not users) since the table represents a type. Columns use snake_case (first_name not firstName). Foreign keys follow the pattern fk_table_referencedtable (fk_orders_customers). Primary keys follow pk_table (pk_users). Unique constraints follow uq_table_column (uq_users_email). Check constraints follow ck_table_condition (ck_orders_price_positive). This convention makes the purpose of each object immediately clear from its name. Consistency matters more than the specific convention chosen.

A surrogate key is a database-generated identifier with no meaning outside the database (auto-increment INT, UUID). A natural key is a value that exists in the real world and means something (ISBN, Social Security Number, email address). Use surrogate keys for most tables because they do not change when real-world identifiers change, they have no business logic attached, and they are compact. Use natural keys when the business already has a stable identifier that genuinely identifies the entity and is unlikely to change, like an ISBN for books or a country code that appears in external systems. Natural keys that appear in URLs or APIs should be treated as immutable contracts.

The expand-contract pattern safely migrates existing columns in phases: Phase 1 (expand) add the new column alongside the old one, deploy code that writes to both, backfill new column from old. Phase 2 (migrate) switch reads to new column, deploy code that reads from new column only. Phase 3 (contract) remove old column in a separate deployment. This pattern is necessary when you cannot safely backfill a column in a single operation on large tables, when the migration might require changing data types that cannot be done in place, or when you need to maintain zero-downtime operation throughout. The pattern adds complexity so only use it when the table is too large for direct ALTER TABLE.

Use a separate lookup table (statuses) when you need to store additional attributes about each status (status_name, display_order, color_code, is_active), when you want referential integrity to prevent invalid status values, or when the set of values changes frequently. Use a column with CHECK constraint (status VARCHAR(20) CHECK (status IN ('active', 'inactive'))) when the set of values is fixed and rarely changes, when you do not need additional attributes per status, and when simplicity outweighs the flexibility benefit. The lookup table approach is correct for domain entities. The CHECK constraint approach is correct for simple enumerated values that are never queried independently.