Designing Game Store
In this part we will actually go ahead and design couple different relational databases. We will apply the principles of relationships and normalization when designing the databases.
Example 1: Game Store
To start with the journey on designing relational databases, let's start with a very basic example. In this example you will see a table / excel spreadsheet that a game store could use to store customer orders:
| orderedAt | firstName | lastName | game | price | |
|---|---|---|---|---|---|
| 2020-02-05 14:12:00 | Laurena | Dyson | laud@mymail.com | Loop Hero | 9.99 |
| 2021-01-04 10:12:00 | Wiley | Sorrel | wlls@mymail.com | Civilization V | 23.00 |
| 2021-02-05 14:12:00 | Laurena | Dyson | laud@mymail.com | Civilization V | 23.00 |
We can see that the store stores customer name, email and order date. Then we have information about the game that the user bought: name of the game and it's price.
In this table we can see only three customer orders, but let's play with the idea that the store has had over 15 000 customers and their excel spreadsheet of customer orders starts to break apart. They really need a better tool for handling all the customer data. So we agreed on creating an app for adding, removing and inserting this data. We will now focus on moving this data structure and data into relational database. (the app you can make yourself on your spare time as a hobby project ;))
It all starts with normalization
As you become familiar on creating relational databases, you don't need to follow these steps as you can pretty much work your way through this on your instinct. But until then, we want to make sure now that we follow the normalization rules.
1st Normal Form
Let's start with the 1st normal form:
- Each field contains only single value: Has been achieved already
- The order of the data does not matter: Has been achieved already
- Values stored in a column must be of same type: Has been achieved already
- There should never be duplicate records: This we have not yet achieved
Duplicate Records
We can start by identifying that our table should be called orders as it contains customer orders. Easiest way to make each record unique in the table is to add a primary key called orderId for the table and identify each record with it's own ID.
This is how we could start with to follow the first normalization rules:
| orderId (primary, auto increment) | orderedAt | firstName | lastName | game | price | |
|---|---|---|---|---|---|---|
| 1 | 2020-02-05 14:12:00 | Laurena | Dyson | laud@mymail.com | Loop Hero | 9.99 |
| 2 | 2021-01-04 10:12:00 | Wiley | Sorrel | wlls@mymail.com | Civilization V | 23.00 |
| 3 | 2021-02-05 14:12:00 | Laurena | Dyson | laud@mymail.com | Civilization V | 23.00 |
And right now, 1st normal form is looking good!
2nd Normal Form
So, in order for our database to conform to the 2nd normal form, it must:
- Be in 1st normal form: This we have achieved
- Each table should contain information only about a single entity (no partial dependencies): Not yet achieved
Only Single Entity in a Table
As we can see, our customers table currently contains information of orders and customers.
We can now identify that we need to split our table orders into two different tables in order to achieve this: customers and orders. Then, in order to make the records unique, we need to add ID for the customers and orders tables.
Here's the customers table that we would need to create:
| customerId (primary, auto increment) | firstName | lastName | email (unique) |
|---|---|---|---|
| 1 | Laurena | Dyson | laud@mymail.com |
| 2 | Wiley | Sorrel | wlls@mymail.com |
And here's the orders table which we would need to create:
| orderId (primary, auto increment) | customerId (foreign key, references customers(customerId)) | orderedAt | game | price |
|---|---|---|---|---|
| 1 | 1 | 2020-02-05 14:12:00 | Loop Hero | 9.99 |
| 2 | 2 | 2021-01-04 10:12:00 | Civilization V | 23.00 |
| 3 | 1 | 2021-02-05 14:12:00 | Civilization V | 23.00 |
2nd normal form is now done. Great!
3rd Normal Form
In order for our database to conform to the 3rd normal form, it must:
- be in 2nd normal form: done!
- have no other non key attribute that needs to be changed when changing another non key attribute (no transitive dependencies)
Transitive Dependency
When looking at the orders table, we can determine that each order does have a name of the game and game has a price set to it. So price of the game is dependant on the name of the game and game name is dependant on the orderId. So we have a transitive dependency over here.
To fix this issue, we need to split the game and it's information into a new table called games.
Games
| gameId (primary, auto increment) | name | price |
|---|---|---|
| 1 | Loop Hero | 9.99 |
| 2 | Civilization V | 23.00 |
Orders
| orderId (primary, auto increment) | customerId (foreign key, references customers(customerId)) | orderedAt | gameId (foreign key, references to games(gameId)) |
|---|---|---|---|
| 1 | 1 | 2020-02-05 14:12:00 | 1 |
| 2 | 2 | 2021-01-04 10:12:00 | 2 |
| 3 | 1 | 2021-02-05 14:12:00 | 2 |
Customers
| customerId (primary, auto increment) | firstName | lastName | email (unique) |
|---|---|---|---|
| 1 | Laurena | Dyson | laud@mymail.com |
| 2 | Wiley | Sorrel | wlls@mymail.com |
Final Design
Great! And just like that we have now created our first relational database :) Here is also a complete reference of what we just created:
And here you can see the same table with relationships:
Conclusion
- Relational databases can be created from existing non-relational databases or from a stratch
- When starting out, it is a good idea to check the normalization rules to make sure that you conform with them
- Foreign key references another table's primary key. For example a customerId in the orders table can refer to the customerId in the customers table.