Relational Database

What is a Relational Database?

Relational Database was first invented or conceptualized by E. F. Codd at IBM in 1970. It is a database model structured to store data in pieces (separate tables) that are “related” to each other. The relationship between the tables are defined by Primary and Foreign Keys. It is focused around the idea of keeping individual table sizes to a minimum while avoiding duplicating data storage. Each table will have one or more field which in combination defines the “primary key” of the table. Each primary key combination should only exist once in the table, this means that the total count of record of the table should return the same result as the count of distinct primary key combinations. Other non-primary key combinations of fields can correspond to a “related” table which has that combination as primary key.

Types of Relational Database?

For example, imagine we would like to create a customer sales order database containing information such as sales product, quantity, price, and sales time. They all can contain millions of sales order transactions. One approach is to just have one consolidated table containing everything. The company may serve only a limited number of products (say 5), and each product is priced the same regardless of customer. So in this one table approach, under product price field we will have identical value for the same product, repeated and stored millions of times. It will then take up storage space and can significantly slow down our query speed. The Relational Database approach is to create two separate tables, known as Entities, one called Product, that just have 5 records in it with Product ID as primary key and Price as a reference field.

The other table can be called Sales Transaction with Transaction ID as Primary Key and has a reference field Product ID. Under this approach, whenever product price information is needed at the sales transaction level, the database will look at the Product ID field (Foreign Key) within Sales Transaction, look for the matching Product ID field in Products (Primary Key), and lookup the corresponding product price. Under this approach, we avoided saving the price field repetitively, and in the case we have to change a product price, we only need to do so for one record in the Products entity, instead of updating all relevant records under the single large table approach.