Advanced SQL

Chapter 4. Advanced Data Modeling with SQL

Modern data platforms depend on strong data models to organize information efficiently and meet business requirements. This chapter explores both classic SQL modeling methods and new developments in semantic modeling. We begin with established techniques such as third normal form normalization, star and snowflake dimensional schemas, and the one big table approach, then move on to knowledge graphs and semantic models.

Throughout, we use practical examples in MySQL and other open-source tools, set in a Human Resources context, to make each concept clear and actionable. By the end, you’ll have a practical understanding of the strengths of each modeling style and see how SQL remains fundamental even as knowledge graphs and other new paradigms become more common.

Traditional SQL Data Modeling Techniques

Early database design focused on organizing data in relational tables, guided by rigorous principles. The goal was typically to minimize redundancy, ensure data integrity, and optimize transactional operations. Let’s review three cornerstone techniques that dominated the industry in past few decades: Third Normal Form (3NF) normalization, the star schema (and its variant, the snowflake schema), and the recent, popular approach of One Big Table (OBT) denormalization. We’ll use a simple HR example—employees and departments—to illustrate these.

Third Normal Form—Structured, Relational Modeling

Relational databases often start with a normalized ...