Data Modeling
I’m starting a series devoted to database structure and modeling. Databases are a key component of development. They help support and ensure the integrity of your information and provide access to join that information in a table.
A data model organizes different data elements and standardizes how they relate to one another. Data models are composed of entities. Each entity has attributes/details that the user wants to track. Such as an id or a name. Below are some key features of data modeling to keep in mind when building your model.
There are three types of data models. Physical data model, Conceptual data model, and Logical data models.
- Physical data models: How the data is physically stored in the database described by the framework or schema.
- Conceptual data model: Is how the user’s view of the data in question
- Logical data models: Are physical and theoretical. Logical data models allow the logical representation of data to exist part from the physical storage.
We design databases in a way that reduces data redundancy without sacrificing integrity. This is done through database normalization. Besides reducing redundancy, it also reduces complexity, ensures relationships between the tables, and ensures data dependencies so that the data is stored logically.
Another important feature of data modeling is the use of primary keys. Also known as surrogate keys. These keys replace natural keys. Data modelers create surrogate keys, which aid in identifying records, building SQL queries, and enhancing performance.
Types of Critical Relationships Types:
- Identifying: A relationship line normally connects parent and child tables. If a child’s table reference column is part of a table’s primary key, the tables are connected by a thick line.
- Non-identifying: We identify tables that are not part of the table’s primary key with a connected dotted line. This signifies a no-identifying relationship.
- Self-recursive. A recursive relationship is a standalone column in a table. It is connected to the primary key in the same table.
Common Data Modeling Errors
- Overuse of surrogate keys. Surrogate keys should be used when the natural key cannot fulfill the role of a primary key
- Missing purpose. If a situation occurs when the user is unsure about the mission or goal of the model.
- Too much denormalization. Although denormalization improves readability. It could create too much redundant data, which is a challenge to maintain.
Resources:
- IBM: What is Data Modeling
- Credera: Data Modeling Explained
