databases like im 5: sql and creating a database (article 2)

ai like im 5

8 min readMar 22, 2024

before i start, a couple things:

this series is purely about databases, data storage and retrieval, there is not a true relation to machine learning and deep learning so feel free to skip over it.
i am going to be encountering a lot of this in my career, so i figured i would dive deeper and provide an amazing source for people like myself that struggled in the beginning!
there is some minor code. it will be intimidating… here is a great article to help break through the coding iceberg: https://medium.com/@ailikeim5/code-like-im-5-but-how-do-we-actually-talk-to-computers-article-1-05019637d5fa

anyways,

now, we can finally dive into that thing we really can’t agree on how to pronounce.

recap

in the last article, we dove into relational databases, and came up with a million dollar app idea:

an app that allowed restaurant workers to trade food with each other!

when designing a relational database, we learned about:

tables and primary keys

foreign keys and some of that beauty

learning that relation databases can be like dominoes if they are not built perfectly from the beginning

so we would design well thought out schemas (plans)

and refine that schema by normalizing our database, adding additional tables, constraints on our relationships, and more.

building a foundation that is scalable, secure, and well designed!

although our database is great, there is still some more work and normalization we can do!

but before diving into building an even better foundation, let’s dive into and learn more about sql and how to actually create the thing we drew above.

a recap on programming languages

remember, establishing communication with our machines is incredibly important, because they only speak in machine code

but this machine code, called binary, is just zeros and ones
i.e no sane human can actually communicate or build any thing significant

so we built these magic translators called programming languages to help with that, and give our machine commands

but these programming languages are intense, it takes a while to learn the secret handshake, and they follow a very clear pattern:

i.e -> and a lot of programming languages do the same thing in just a “different font”

luckily for relational databases, we moved to a standard programming language:

sql

i don’t even have to write anything yet because this is an amazing video by one of the best creators on youtube
don’t worry if you can’t make sense of the full thing yet

but notice, a very interesting thing, even in a standardized language, there are many different types of sql or flavors as i will call them!

these are going to help us build and management our database
they are the environment and vessel for sql execution
each one offers slightly different features, does things slightly different, and will have slightly different sql syntax

or in other words, two programmers trying to agree on something even when that thing is a standard:

luckily for us, we don’t have to dive to deep into each flavor of sql

i.e -> for a beginner, the differences in syntax are very minor, and not noticeable until you make it to the advanced stuff.
but if you want to a database that is optimized up the wazoo, dive deep into 1 and master that one.
so which 1 should i chose?

postgres sql

postgres is easy for beginners and loved by developers
super easy to setup, access, and maneuver once you get the hang of it
and it offers many cool features, is open source (i could rant about this hours), and more.

and most importantly, it’s logo is a doodle of an elephant

now we can dive into some actual code/sql!

sql is a bit different than most programming languages

sql is called a declarative language for this reason

we don’t really tell it how to do something, we just want tell it what the do

but what does that mean?

everyone has their own comparison but funny enough i think the best way to think about this is a food delivery app like doordash

there’s a certain magic to this declarative nature like there is to food deliver services
they handle all the work and make everything simple, but we don’t really know what’s going on behind the scenes

or in other words:

and although there’s beauty in this simplicity, there’s a tradeoff of it too. (i.e -> that doordash cost me a kidney)

installation and setup of postgres and sql (if u never used before)

there is no point in me fully explaining installation of softwares
it is device specific and there are so many great sources out there

but here is a general guide:

we are going to be working with a cool tool inside postgres called pgadmin, make sure it gets installed, this give us better visualization and control of our database, especially as a begineer.

great sources:

windows:

mac:

creating a database

creating a database is super simple in pgadmin4
all we have to do it click on the name of our server (it should be called the postgres version you install unless you change it )
right click on databases and click create database

all we need is a name for now, don’t worry about the other stuff!

and we unlocked all this cool stuff

and now we can fill it in:

queries

queries are requests we make towards the database!
or in plain english: queries are commands, they are how we execute sql code to fill in our database, search for stuff, and do some really cool stuff we will talk about in the future.
pgadmin gives us a simple way to query our db, called the query tool
i.e -> this fancy button right here

let’s look at commands we can do
don’t stress syntax, you learn it as time goes on, it is very hard for a beginner!

creating tables

creating a table with sql is super simple

what does this fully look like?

before we put stuff inside, here is an awesome article if you can’t wrap your hand around datatypes and how a computer generally stores things: link
datatypes are like a toolkit, you wouldn’t use a hammer to a screwdrivers job, especially if you are a big company and messed up datatypes could cost you millions of dollars
every programming language does it different, so sql will too

sql datatypes

hopefully this isn’t rocket science.
like i said, we do not use a hammer to do a screwdrivers job!

constraints

remember our friend borat was very important to the database, we are going to need to tell our db when he is foreigner, and when he is a primary
so when we create a table, we can add some constraints and keywords to each

creating our users table:

for reference:

so let’s write that query

again don’t stress the syntax, datatypes, and more for now, it will take time for it to click.
it is good practice to put foreign keys on the bottom
and if we run the code with the play button, we will have successfully executed everything.

our tables will start out completely empty so we have to

fill in the data

creating a simple visual schema of our database in postgres

pgadmin is really cool
if we right click on our database, we can click on erd for database and generate a amazing visualization of our database and the tables we just created.

this shows our primary and foreign keys, how they are connected, and more
giving us a good picture of these tables

all that drawing i did was for nothing noooo???

well kinda, you will see later how this tools has its faults!
but seriously pgadmin and a lot of these database tools will make our lives so much easier once we get the secret handshake