Relationships

1:1

• One record in a table relates to only one record in in another table and vice-versa
• Example: 1 customer <-> 1 customer_detail

• A 1:1 relationship can usually be collapsed into a single table

• Reasons to 1:1 split

• Optional / sparse data (vertical partitioning): If customer_detail holds columns that only apply to some customers (e.g. KYC documents, extended profile), splitting keeps the main customer row narrow and avoids a table full of NULLs.
• Performance (hot vs. cold columns): Frequently-read columns stay in customer; rarely-accessed or large columns (BLOBs, TEXT, images, bios) go in customer_detail. Narrower rows → more rows per page → faster scans and better cache use for the common queries.
• Access control: You can grant permissions per table. Sensitive fields (SSN, salary, medical data) live in a separate table that only certain roles can read, while everyone can read the base customer.
• Separation of concerns / ownership: Different modules or services own different tables. Auth owns customer (credentials), a profile service owns customer_detail.
• Class-table inheritance: A base customer with specialized 1:1 subtype tables (individual_customer, business_customer) — each subtype has its own columns.
• Avoiding schema churn / column limits: Some engines have per-row size or column-count limits; splitting sidesteps them.

1:Many

• One record in a table relates to many records in another table (and not vice-versa)
• Example: 1 book -> n reviews

Many:Many

• One record in a table relates to many records in another table and vice-versa
• Example: 1 author -> n books, 1 book -> n authors
• Needs a intermediary table!