Part 1. The first part of Databases for beginners gives an introduction to databases and PostgreSQL. Basic database concepts will be defined and it will help you set up your first database in the cloud.
A database allows you to store information related to a specific topic in an organised way. It's great when you need to store a searchable collection of data/information in a computer system. Databases are used in a lot of systems today. Almost every web shop has a database with their products, almost every site with a lot of users uses a database to store user information. A blog usually has a database containing all blog articles. All your contacts in your phone are probably saved in a database in your phone and all comments on Facebook are saved in a database. A relational database is a database that presents information in tables with rows and columns, and that's what we will focus on in this article.
Most databases contain one or more tables which may each include several different fields. Tables are uniquely identified by their names and each table is made up of rows and columns, like a grid (as in an excel sheet).
Example database tables
Two example database tables are shown below. One table, called players, include football player information; firstname, lastname, birthdate and country. Another example is the teams table with fields about teams; name and ground.
|1||Manchester United||Old Trafford|
|2||FC Barcelona||Camp Nou|
|3||Chicago Red Stars||Toyota Park Bridgeview|
|4||VfB Stuttgart||Mercedes-Benz Arena|
Entity, fields, record and data value
Each row (also called a record or tuple) in the table represents the information about a specific entity, for example, the player. Each column lists a particular piece of information such as the name or birthdate. We refer to these as fields. Every entity has a set of fields that you use to enter the information about a particular record. For each field in the table, a single item of data that you enter, such as "1990-08-23" in birthdate, is referred to as a data value.
Data stored in a database can easily be searched, sorted, and updated. It’s easy to add a new player to your players table. And it's easy to update the team information in the teams table if, e.g, the home ground has changed.
Every table usually has (and should have) one field in which the data value uniquely identifies the record, called the primary key. Its purpose is to uniquely identify each row in the database, and therefore, two rows can not have the same primary key value. You can explicitly select every single row by just knowing its primary key. The primary key is the player_id and the team_id in the tables above.
Creating an efficient table structure consists of breaking down your fields into simpler and simpler components. You should not need to save the same data in two different tables. When defining an app, it's important to keep in mind and ask yourselves questions like, "What kind of information do we want to store?", "Can we separate our information into distinct categories so that each entity holds only one type?" Answers to this kind of questions will guide you when you design the structure of our application and your database.
The relationships between the entities need to be defined, and the cardinality of each relationship. The cardinality shows how one side of the relationship (e.g players) belongs to the other side of the relationship (e.g teams). One player can, in this case, belong to one and only one team while one team could include many different players. This creates a many-to-1 relation, M:1.
A foreign key is a key used to link two tables together. It's a field in one table that uniquely identifies a row of another table or the same table. I.e the key field in the first table (teams_team_id in the picture below), identifies the primary key in the other table.
We will now add some extra complexity to the design. A player can participate in a game, and a game can have many players. We do get a many-to-many (M:N) relationship between players and games. In a relational database design, a direct many-to-many relationship between two tables is not allowed. You need to break apart the many-to-many relationship into two one-to-many relationships. You need to use something called a "join table" or "reference table". Each record in the "join table" would have the foreign key fields of the two tables it is joining together.
If you want to become a database architect you’d have to become familiar with something called normalisation and normal forms. However, we will not go into these terms in this article. Our task is to create a simple database structure for our information, where it's flexible and easy to extract useful information.
1. Set up your database
It's time to set up a test database and perform some actions on it. PostgreSQL is an open source relational database that you can try out for free here at ElephantSQL. ElephantSQL is a PostgreSQL database hosting service, that will manage administrative tasks of PostgreSQL, such as installation and backup handling.
1.1 Create an account
To get started you need to sign up for a customer plan. The plan option you want to use depends on your needs. In this tutorial guide, we will use the free plan Tiny Turtle so that everyone has a chance to complete the guide. Get started by pressing the Log in button at the top right corner. Sign up by entering your email address and a password and press Sign up.
An email will be sent to you with email address confirmation information. You will have to confirm your email address.
1.2 Create a database instance
Press Create new instance from the instances-view.
You need to specify some information for your new database.
- Name: The name should be a name that helps you identify which application the instance is used by.
- Data center: Choose the datacenter and region of where your instance will be located in the cloud. Choose the datacenter that's closest to the servers of your application - you want the latency to be as low as possible. Datacenters that are available for the free plan is marked with an asterisk *.
- Plan: The plan you would like to have. Tiny Turtle is the name of the free plan.
1.3 Database details
The instance details, such as URL, statistics and active connections can be found on the detail pages for your new database instance. You can find all details by clicking on the instance in the console. You are from the details page able to restore backups of your database and rotate your password. If you are on a dedicated plan (Happy Hippo or larger) you will be able to view server metrics, you can set up followers and you can set up multiple databases on one single ElephantSQL instance.
Once you have created your account you can get started using your PostgreSQL database. Your database can be tested and used via different tools, e.g the ElephantSQL SQL browser, pgAdmin or psql. psql is an interactive terminal for work with Postgres and pgAdmin is a graphical administration client for PostgreSQL.
As always, we welcome your feedback. Please contact firstname.lastname@example.org if you have any suggestions, questions or feedback.