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.

Players

Teams

Database concepts

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 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.

Primary key

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.

Database design

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?, or 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.

Cardinality

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.

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. 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 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 normalization 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 plan. The plan option you want to use depends on your needs. In this tutorial, we will use the free plan Tiny Turtle so that everyone has a chance to complete the guide. Get started by pressing the Login 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 a confirmation request.

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.
• Datacenter: 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 are 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. Postgres uses psql as an interactive terminal for work and pgAdmin as a graphical administration client.

As always, we welcome your feedback. Please email us at contact@elephantsql.com if you have any suggestions, questions or feedback.