Setting Up the Database

I began setting up my database on paper because it is much easier for me to see relationships visually than in code. This was just a very simply diagram of what types of tables I would need to create and how they would relate to one another:

So in this diagram we can see that the users table is independent. The user can go to sites, or do dives, so the user id (u_id) will be put into other tables, but the user table will only hold the user information. So when we set up the database we want to start with the independent tables.

*sidenote: As we can see here, I did not give the users table even close to enough information, I don't log their email address or password (so silly!) and this is something I have to add to my tables later! Luckily it means that I can demonstrate adding columns to tables after the fact!

In our project, we want to create a createTables.sql sheet. This sheet will look like a less pretty version of the diagram above. The createTables.sql looks like this:

Here I define what I want in my tables, and the type of input each field will take. For the most part it will be text, except for the primary key IDs which we set as serial primary key. This means that it will incrementally increase by one with each new entry. Another exception in this example is when a primary key from one table gets put into another table and becomes a foreign key. So for the sites table we are passing a foreign key from the places table.

So now that we have the design skeleton of our database, it is time to create the database! We will be using Postgres as our database. So I saved my Postgres App in Applications. So I need to open up the Postgres App (the elephant) and start. The first thing we need to do in psql is to get into the create a new database with a name, in my case it will be navigatio. This command is:

Then we want to get into the database which is now named ‘navigatio’. We know we are in our database when we see:

navigatio=#

One important command is \dt this shows a list of the tables and relations. When we do that now, we see that there are no relations found. This is because we haven't hooked up my createTables.sql sheet, which sets up the tables for the navigatio database. To do this we have to use another important command \q which exits us out of the current database. Then we need to upload the tables in our createTables.sql

Now when we \dt, we can see all the tables that our database now has! (or \dt+ which just gives us more details about our tables)

Now our database is set up! So our next task is getting our communication lines between the database and our project going!