Designing Relational Databases: E‑R Modeling, Normalization, Storage Engine, and Index Choices

Abstract

This article discusses relational database design using a student‑course selection scenario, explaining the E‑R model, table design, normalization (BCNF and 3NF), storage engine selection, character set and data type choices, and index design.

E‑R Model

Defines entities, attributes, entity sets, relationships, and relationship sets, illustrated with a student‑course E‑R diagram.

Entity : distinguishable objects such as a student or a course.

Attribute : descriptive properties of an entity, e.g., name, age.

Entity Set : collection of entities of the same type.

Relationship : association between entities, e.g., a student selects a course.

Relationship Set : collection of similar relationships.

The resulting E‑R diagram shows student attributes (id, name, age, gender), course attributes (id, name, credit), and the many‑to‑many enrollment relationship with attribute grade.

Relation Schema Design

From the E‑R diagram, relational schemas are derived and normalized. The article introduces Boyce‑Codd Normal Form (BCNF) and demonstrates that the student, course, and enrollment tables satisfy BCNF. It also shows a case where BCNF cannot preserve all dependencies, leading to a 3NF solution.

mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO vc VALUES ('ab  ', 'ab  ');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;
+---------------------+---------------------+
| CONCAT('(', v, ')') | CONCAT('(', c, ')') |
+---------------------+---------------------+
| (ab  )              | (ab)                |
+---------------------+---------------------+
1 row in set (0.06 sec)

Storage Engine Selection

MySQL provides several storage engines; the most common are MyISAM (default before 5.5.5) and InnoDB (default after 5.5.5). The article lists criteria for choosing an engine and notes that using only InnoDB simplifies backup and recovery.

Character Set Choice

Selecting an appropriate character set early avoids costly migrations later. Smaller character sets save space and improve performance; database‑level or table‑level settings are recommended for consistency.

Data Type Selection

Guidelines include using the most precise type, preferring VARCHAR over CHAR for space efficiency, handling TEXT/BLOB with care, and choosing appropriate numeric types (FLOAT/DOUBLE vs DECIMAL, INT variants).

Index Design

Design principles: index columns used in WHERE or JOIN clauses, prefer unique and short indexes, use left‑most prefixes, avoid over‑indexing, and consider function usage on indexed columns.

Example tables for the student‑course scenario are presented with primary keys, foreign keys, and column definitions.

References

Abraham Silberschatz et al., Database System Concepts, 2012.

MySQL 5.7 Reference Manual.

网易技术部 MySQL Chinese documentation.