Key Principles of Database Design: From Entity Relationships to Performance Optimization

1. Relationship between original documents and entities: can be one-to-one, one-to-many, or many-to-many; usually one-to-one where a document maps to a single basic table.

Example: an employee resume corresponds to three basic tables—employee info, social relations, and work history.

2. Primary keys and foreign keys: every entity should have a primary key and a foreign key; they form the core of the data model.

3. Characteristics of basic tables: atomicity, originality, derivability, and stability.

4. Normalization standards: aim for third normal form (3NF) but sometimes denormalize for performance by adding redundant columns.

Example: a product table includes a calculated "Amount" column (price × quantity) to speed up queries.

Product Name

Model

Unit Price

Quantity

Amount

Television

29‑inch

2,500

40

100,000

5. Simple explanation of the three normal forms: 1NF – atomic attributes; 2NF – unique record identification; 3NF – no derived (redundant) fields.

6. Handling many‑to‑many relationships by introducing a junction entity.

Example: a library system uses a "Borrow/Return" entity linking books and readers.

7. Primary key generation methods: surrogate numeric keys are preferred; composite keys should be kept minimal.

8. Correct understanding of data redundancy: repetition of keys is not redundancy; only non‑key duplicate data is.

Example: "Amount" derived from "Price" and "Quantity" is a higher‑level redundancy used to improve speed.

9. No single correct ER diagram; good diagrams are clear, concise, with appropriate entity count and attribute distribution.

10. Views are virtual tables that simplify complex queries, improve performance, and aid data security; view depth should generally not exceed three layers.

11. Intermediate, report, and temporary tables: intermediate tables store aggregated data, temporary tables are session‑specific.

12. Integrity constraints appear as domain, referential, and user‑defined rules.

13. The “Three‑Less” principle to avoid patchy designs: minimize number of tables, composite‑key columns, and total columns.

14. Ways to improve database performance: denormalize, use stored procedures, horizontal/vertical partitioning, DBMS tuning, and algorithmic SQL optimization.

Overall, these fourteen techniques provide practical guidance for balanced database design, emphasizing trade‑offs between redundancy and speed.