Relational Databases (Pt. 2)

raphael krantz
3 min readAug 8, 2020

--

So what makes a relational database relational? Data is stored in a tabular structure consisting of columns and rows. However, even a simple database doesn’t keep all its data in one table. This is for a number of reasons. The data may come from different reports from one tracking system. The data may come from different tracking systems altogether. The data can be scraped from multiple websites. So if the data is being kept in different tables, the tables have to be connected. Let's take a look at a simple example.

Above we have a picture of how various tables of a relational database are related. This type of depiction is called a schema, a map of the organization of data where each box represents a table in a database. On an average day, restaurants across the world have a chain of transactions that looks something like this:

customer arrives > receives a menu> orders> pays > order is prepared (food order sent to the kitchen, drink orders sent to the bar) > receives the order. Each of these steps is governed by data that is kept track of in tables like in the schema above.

Customer arrives: Most restaurants today operate with a Point of sale (POS) terminal. Some details about a customer can be obtained from the credit card used in the sale; others can be gained from online purchases when a profile is created with an email address.

Menu: Restaurants will have many menus: breakfast, lunch and dinner, brunch. Additionally, there are frequently seasonal menus. Halloween, New Year's Eve, St Patrick’s Day often calls for special menus, often with completely different food and drink items.

Orders: Each ticket will have at minimum information regarding food items, quantity, price as well as any modifiers for any items; for example hold the pickles on the cheeseburger.

Payment: The order date, customer info, payment amount, and payment type are captured with credit card transactions.

As in the schema above, each of these is tracked in a different table. Each table possesses a primary key, a unique numerical identifier that differentiates one member of the table from another. For example, if you look at the order table in the schema above, you can see the primary key designated with a key symbol: the OrderID. The OrderID is unique in this table. Below OrderID, another piece of data captured is the CustomerId. CustomerId is the primary key of the customer table and it is through the CustomerId that the two tables are linked.

Each piece of data captured is of a type. There are many datatypes and relational databases will specify how datatypes are named and defined. Below you can see the nomenclature for three commonly used relational databases: MySQL, PostgresSQL, and SQLite:

Next, I’ll walk through how to set up an SQL environment on your laptop. See you next time!

--

--

No responses yet