Skip to main content

Database Design Principles

· One min read
Hinny Tsang
Hinny Tsang
Data Scientist @ Pollock Asset Management

Data base design principle

Data Integrity​

  • Accurate and consistent data is maintained throughout the database.

Normalization​

  • Data is organized to reduce redundancy and improve data integrity.

Normal Forms:

Denote functional dependency for table A, B, C, D, E, F, G

A, B -> C, D, E, F, G
A -> C
E -> F

Etc.

  • 1NF: Atomic

  • 2NF: No Partial Dependency

    • Any feature cannot be determined by only part of the primary key.

  • 3NF: No Transitive Dependency

    • If A -> B and B -> C, then A -> C should not hold (transitive dependency)

  • BCNF: No Non-Trivial Functional Dependency

  • 4NF: No Multi-Valued Dependency

  • 5NF: No Join Dependency

Scalability​

Performance Optimization​

Flexibility and Extensibility​

Security​

Atomicity​

Referential Integrity​

Naming Convention​

Documentation​

Entity-Relationship Model

Dimensional Data Model

Used in data warehouse design. Comprised of facts and dimension table.

Goal is to enable efficient querying (SELECT operation).

Used in a lot of Online Analytical Processing (OLAP) systems (e.g. Cognos, Tableau).