Models, Relations, and Associations… A story of Relational Databases

I am sure all of you now are very familiar with the concept of domain modeling, and identifying your different models and the relationships they share…
For me these concepts were very familiar, I used this design pattern somewhere before… so because it is always good to scratch the surface and dig into concepts we learn and not take them for granted, and because since I joined the Flatiron Software engineering Bootcamp I picked up a pattern of starting with the beginnings of things, learn the basics first, I decided to take us back to the beginnings of data modeling and the relational concept.

Going back to when Martin Fowler named the active record pattern in his book “Patterns of Enterprise Application Architecture” in 2003, we notice that he described it as “An object that wraps a row in a database table or view, encapsulates the database access, and adds domain logic on that data”[1. Fowler, Martin (2003). Patterns of enterprise application architecture]. So, Databases were there first… and the term Relational Database was invented by Edgar Frank Codd at IBM in 1970, and he introduced it in his research paper “A Relational Model of Data for Large Shared Data Banks” [2. E. F. Codd (1969–1970). A Relational Model of Data for Large Shared Data Banks]. Before that starting in the 1960s databases started as really complicated informational structures. Two main databases quite different in their modeling paradigm were known:

1- the hierarchical database, simple but inflexible because of the relationship is confined to a one-to-many relationship. In this data model, the data are organized into a tree-like structure where each child record has only one parent, whereas each parent record can have one or more child records… more importantly in order to retrieve data from this model, the whole tree needs to be traversed starting from the root node.[3. Asbjorn Danielsen, (1998) The Evolution of Data Models And Approaches to persistence in Database Systems], the hierarchical architecture lost traction after Codd’s relational model became the standard, but it resurfaced with XML in the late 1990s [4. Michael J. Kamfonas/Recursive Hierarchies: the Relational Taboo]

2- the network database which arranges its data as a directed graph and has a standard navigational language, which made it possible to move directly from one specific entry point in a data set to another record in another data set.
even though this is a more flexible design and it can represent 1:1, 1:N, and N:M relationships the developer still needed to know the physical representation of the database and applications using a network database has to be changed every time the structure of the database changed.

Now, what is a relational database, what did it change, and how to make a database relational…

The relational database has a tabular presentation of its data, where each row in the table is a record that has a unique ID called the primary key. The columns represent attributes and every record has values for those attributes that help establish the relationships between data points.

What differentiates the relational database from its precedents is the separation between the physical storage structure and logical data structure, which allows the flexibility of operations reading, writing, and updating the data…

ACID, four crucial properties that define relational database transactions.

What makes a database relational and what is normalization?

Since the first introduction of the relational database and throughout the years the concept was maturing more and more and different forms of normalizations got introduced…

[Database normalization is the foundation of relational databases. It is the process that organizes data into tables that can then be connected to each other. Each table should be about a specific topic, and only those columns that support the topic are included in the table.
Database normalization provides the following benefits:

1. Duplicate data is minimized, and the needs of data storage decrease.

2. Changes to data must occur in only one place.

3. Access to the data is quicker and more efficient.

Once the data schema and business rules for the database are established, the data is processed in steps that create tables and primary keys, eliminate repetitive data and build relationships between the data by splitting the data into new tables and creating foreign keys for those tables. The steps are called First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF). Each step attempts to further refine the data and narrow the focus of each table.]

Properties introduced by every Normalized Form.

Step-by-step normalization.

In order to satisfy the 1st Normal Form:

1- All our columns should have atomic data, which means one value per column per record.

2- Data in a column should be homogeneous, or in other words, must be of the same type or kind.

3- The columns should have unique names, in other words, no columns share the same name.

4- The order of our columns does not matter.

2nd Normal Form:

1- Should be in the 1st Normal Form.

2- Should not have Partial Dependency, which in plain English means, make sure all data in your table belongs to and are related to your primary key especially when you have a composite primary key.

3rd Normal Form:

1- Should be in the 2nd Normal Form.

2- Should not have a transitive dependency. This happens when you have non-prime attributes depending on non-prime attributes rather than depending upon the prime attributes or primary key.

Conclusion

Now, with the development and maturing of the Relational Databases, the Object-Oriented development was on the rise too; but that’s another story…

What is important to know though, is that in order for those two essential concepts of development to work together, the idea of ORM surfaced in 1995 with TopLink.

ORMs came to bridge a gap between two separate incompatible systems both very important and essential in the lifecycle of software and for the benefit of software engineering.

I hope this article helped throw some light on the development and maturing of this major and career and I hope it got as excited as I am to see what to come and how Software Engineering will change in the upcoming years.

Software Engineer, Problem solving oriented and new technologies enthusiast.