1 – Data Modeling
Data modeling includes many skills and techniques. You start data modeling by organizing data into tables. Each table should qualify a single subject.
After you identify single subject tables, you need to normalize the table. There are several steps to normalization. The following are the core steps to achieve third normal form:
- You can achieve First Normal Form (1NF) by doing two things:
- Columns should contain only one thing, like a scalar variable. A scalar variable can be a number, string, date, or timestamp.
- All columns together should uniquely identify each row in the table.
- You can achieve Second Normal Form (2NF) by starting with a 1NF table and by doing two things:
- Identify a set of key columns that uniquely identify each row of the table. The unique columns constitutes a natural key.
- Verify there are no non-key columns that depend on part of the natural key. You need remove the key and non-key columns from the table and put them in a new table. Then, you need to replace the original key column value with a foreign key that matches the primary key column of the new table.
- You can achieve Third Normal Form (3NF) by starting with a 2NF table and by doing:
- Verify there are no non-key columns that depend on other non-key columns. If you discover a functional dependency between two non-key columns. A functional dependency occurs when one non-key column depends on another. You need to remove both the functional dependency and functional dependent columns to a new table. Then, you need to replace the original functional dependency column with a foreign key value that points to the newly created table of previously non-key columns.
You can read Chapter 3 of the MySQL Workbench: Data Modeling & Development explains these concepts in more detail. It also qualifies both domain key modeling as well as normalization practices.
