4 Core Concepts You Need to Understand SQL Databases

Database SQL Architecture Modernized Technology local_offer

We developers live in an object oriented world where data is represented as objects in our applications. However, these applications often end up saving their data in SQL databases which are relational, not object oriented. This often causes translation errors and could cost you and your team significant time tracing and debugging. Understanding keys, relationships, and cardinality are essential skills for any developer to be able to create and maintain reliable, consistent, and efficient applications by being able to create and read data models and spot potential problems before they become major bugs.

1. Tables

The most basic component to understanding SQL databases is understanding what a table is. A table is a unique set of data with a consistent number of typed data attributes, which are called Columns. Each table must have a primary key, which is a column which uniquely identifies a row - otherwise, it would be impossible to find a specific row in a table. In the example below, the # denotes the primary key (id), but each the style changes based on what tool you use to make your data model, which is a visual representation of an SQL database which lays out the tables and relationships between them.

2. Relationships

The power of relational databases is (perhaps obviously) in the relationships between these tables, which allows you to link together data in numerous ways. When two tables are joined, one is always considered the 'parent' in the relationship and the other is the 'child.' The primary key of the parent table becomes a column in the child table, which is called a foreign key. For instance, a person may have an address, so when a relationship is created between them, the primary key from the parent table (in this case PERSON) is migrated to the child table (in this case ADDRESS) as a foreign key (ADDRESS.person_id).

3. Ordinality

Once you understand that there is a relationship between two tables, you are ready to build an understanding of what the relationship means. One place to start is considering the Ordinality (also known as Modality) of the relationship, which indicates whether the relationship from the parent table to the child table is optional or not. For instance, the relationship between a book and author is mandatory, while the relationship between a book and an owner would be optional, since a book can be unowned, but somebody must have written it. This is represented by having an O on the relationship if it is optional (Zero or more) or a straight, perpendicular line, which indicates it is required (One or more). Ordinality represents only the parent side of the relationship.

4. Cardinality

The next step in understanding relationships is learning about Cardinality, or multiplicity, of the relationship, which describes the child side of the relationship. The Cardinality is either one-to-one (A person has an address), one-to-many (A person has multiple addresses) or many-to-many (A person can have many addresses, and an address can belong to many people). To implement a many-to-many relationship, there must be an associative table in-between the other two tables which act like a lookup or a table of contents, to link the tables together. This is necessary; otherwise, it would not be possible to look up a single row in a many-to-many relationship.

Cardinality and Ordinality only scratch the surface of database structure. After understanding these concepts, one can move on to understanding more advanced concepts such as Identifying relationships and Normalization. However, with just a basic understanding of the types of relationships, you should have no problem picking up a data model and making sense of not just the 'what' but the 'why' as well. These skills make you a stronger, more adaptable program, able to break down complicated issues through multiple systems and find a holistic solution to the problem.

Learn more about modernized technology here:

Interested in training to help advance your agile journey? Click the button to view our current list of public training courses! Use code BLOG10 for 10% off!