Relational schema design

In relational databases, we design with the goal of avoiding anomalies and redundancy. Anomalies can happen when we have the same information stored in multiple columns; we update one of them but not the rest and we end up with conflicting information for the same column of information. An anomaly can also happen when we cannot delete a row without losing information that we need, possibly in other rows referenced by it. Data redundancy can happen when our data is not in a normal form, but has duplicate data across different tables, which can lead to data inconsistency and is difficult to maintain.

In relational databases, we use normal forms to normalize our data. Starting from the basic 1NF (first normal form), onto the 2NF, 3NF, and BCNF, we model our data taking functional dependencies into account and, if we follow the rules, we can end up with many more tables than domain model objects.

In practice, relational database modeling is often driven by the structure of the data that we have. In web applications following some sort of MVC model pattern, we will model our database according to our models, which are modeled after the UML diagram conventions. Abstractions such the ORM for Django or the Active Record for Rails help application developers abstract database structure to object models. Ultimately, many times we end up designing our database based on the structure of the available data. Thus, we are designing around the answers that we can have.