Fundamental Principles of Database Design: Entities, Keys, Normalization, and Optimization

1. Relationship between source documents and entities – A source document can map to entities in one‑to‑one, one‑to‑many, or many‑to‑many ways, typically one‑to‑one; special cases involve multiple entities per document or multiple documents per entity.

Example: an employee's resume may correspond to three basic tables (personal info, social relations, work history).

2. Primary and foreign keys – Every entity should have a primary key (PK) and a foreign key (FK); in ER diagrams leaf entities may omit a PK but must have an FK to link to a parent. Keys are central to global database design.

3. Properties of basic tables – Basic tables differ from intermediate or temporary tables by four traits: atomicity, originality, deductibility, and stability, which guide their design and distinction.

4. Normalization standards – While third normal form (3NF) is ideal, practical designs may relax it to improve performance by adding controlled redundancy (e.g., storing a calculated "amount" field).

5. Plain‑language interpretation of the three normal forms – 1NF enforces atomic attributes, 2NF ensures record uniqueness, and 3NF eliminates derived (redundant) fields; however, limited redundancy can be acceptable for speed.

6. Handling many‑to‑many relationships – Convert many‑to‑many links into two one‑to‑many relationships by introducing a third associative entity (e.g., a "BorrowReturn" table linking books and readers).

7. Primary key value selection – PKs can be meaningless auto‑increment numbers or meaningful fields; composite PKs should be kept minimal to avoid large indexes and slow performance.

8. Correct understanding of data redundancy – Repetition of PK/FK across tables is not redundancy; true redundancy involves non‑key fields, especially derived fields (high‑level redundancy) versus duplicate raw fields (low‑level redundancy).

9. No single correct ER diagram – ER diagrams have no unique answer; they must cover business requirements, be clear, concise, and avoid low‑level redundancy.

10. Use of view technology – Views act as virtual tables, providing a secure, aggregated window onto base data; they should be limited to three layers deep for manageability.

11. Intermediate and temporary tables – Intermediate tables store aggregated data for reporting; temporary tables are personal, programmer‑maintained structures.

12. Integrity constraints – Include domain integrity (CHECK), referential integrity (PK/FK/triggers), and user‑defined integrity (procedures, triggers).

13. The “Three‑Less” principle – Reduce the number of tables, composite‑key columns, and total columns to avoid patch‑style designs and improve maintainability.

14. Ways to improve database performance – Lower normalization level, add redundancy, limit triggers, use stored procedures, perform heavy calculations outside the DB, partition large tables horizontally or vertically, tune DBMS parameters, and apply optimized SQL algorithms.

Overall, these fourteen techniques, derived from extensive practice, guide developers to balance data redundancy with processing speed, achieve clean ER models, and maintain scalable, efficient database systems.