Key Principles of Database Design: Primary/Foreign Keys, Normalization, and Table Types

1. Relationship Between Original Documents and Entities

An original document can correspond to an entity in a one‑to‑one, one‑to‑many, or many‑to‑many manner; typically it is one‑to‑one, but special cases exist where a single document maps to multiple entities or multiple documents map to a single entity.

Example: An employee's resume may correspond to three basic tables—EmployeeInfo, SocialRelations, and WorkHistory—illustrating a one‑document‑to‑multiple‑entities scenario.

2. Primary Keys and Foreign Keys

In an ER diagram, leaf entities may define a primary key or not, but they must have a foreign key because they have a parent entity.

Designing primary and foreign keys is crucial for global database design; a renowned database expert once said, “Keys are everywhere; without them there is nothing.”

Because a primary key abstracts an entity, pairing it with a foreign key represents the connection between entities.

3. Properties of Base Tables

Base tables differ from intermediate or temporary tables by possessing four characteristics:

Atomicity – fields cannot be further decomposed.

Originality – records store raw, foundational data.

Derivability – data combined with code tables can generate all output data.

Stability – structure is relatively stable and records are kept long‑term.

Understanding these properties helps distinguish base tables from other table types during design.

4. Normal Form Standards

Base tables should ideally satisfy the Third Normal Form (3NF), but strict adherence may not yield the best performance; sometimes controlled redundancy is introduced to trade space for time.

Example: A product table containing a redundant "Amount" column (price × quantity) speeds up queries despite violating 3NF.

5. Plain Explanation of the Three Normal Forms

In simple terms:

1NF – attributes must be atomic.

2NF – each record must have a unique identifier (entity uniqueness).

3NF – no attribute should be derivable from others (no redundancy).

While a fully non‑redundant design is possible, modest redundancy is often accepted to improve efficiency.

6. Recognizing and Properly Handling Many‑to‑Many Relationships

When two entities have a many‑to‑many relationship, introduce a third entity to transform it into two one‑to‑many relationships, distributing attributes appropriately.

Example: In a library system, "Book" and "Reader" have a many‑to‑many relationship; a "BorrowReturn" entity with attributes like borrow time and status, plus foreign keys to both Book and Reader, resolves the issue.

7. Methods for Assigning Primary‑Key Values

Primary keys can be system‑generated meaningless strings or meaningful field combinations; the former is generally preferred. When using composite keys, keep the number of fields low to avoid large indexes and performance loss.

8. Correct Understanding of Data Redundancy

Repeated appearance of primary/foreign keys across tables is not data redundancy; true redundancy involves non‑key fields, either low‑level (duplicate data) or high‑level (derived data).

Example: In a product table, "Amount" derived from "UnitPrice" × "Quantity" is high‑level redundancy introduced to speed processing.

9. No Standard Answer for ER Diagrams

An ER diagram has no unique correct answer; it must cover business requirements and be clear, concise, appropriately sized, and free of low‑level redundancy.

10. View Technology in Database Design

Views are virtual tables built on base tables, providing a programmer‑friendly window, enabling data integration, improving performance, and supporting security by restricting direct base‑table access.

11. Intermediate, Report, and Temporary Tables

Intermediate tables store statistical data for data warehouses or reports and may lack primary/foreign keys; temporary tables are personal, automatically maintained by programmers.

12. Integrity Constraints

Three aspects of integrity:

Domain integrity – enforced with CHECK constraints.

Referential integrity – enforced with PK, FK, and table‑level triggers.

User‑defined integrity – business rules implemented via stored procedures and triggers.

13. Avoiding Patch‑Style Design with the “Three‑Few” Principle

1) Minimize the number of tables in a database. 2) Keep the number of fields in composite primary keys low. 3) Reduce the total number of fields per table.

This balances data redundancy and processing speed, encouraging integration and preventing a chaotic “patch‑work” database.

14. Ways to Improve Database Runtime Efficiency

Key tactics include:

During physical design, lower normalization, add redundancy, avoid triggers, and use stored procedures.

For extremely large or complex calculations, process data outside the DBMS (e.g., with C++) before loading.

Horizontally partition tables with massive rows or vertically partition tables with many columns.

Optimize DBMS parameters such as buffer counts.

Write efficient SQL algorithms.

Overall, performance gains require simultaneous effort at system‑level, design‑level, and application‑level optimizations.