Database introduction
What is a database?
Is a collection of organized data that can be easily accessed and managed
When it comes to databases we don't really interact with the database directly
Instead we have a Database management system (DBMS)
After which the DBMS will send the data over to the Database and return the results
So... we always have a piece of software that interacts with the database
Types of Databases
Relational Databases
MySQL
PostgreSQL
Oracle
SQL Server
NoSQL
MongoDB
DynamoDB
Oracle
SQL Server
Relational Databases & SQL
Structured Query Language (SQL) is the language used to communicate with the DBMS
Each instance of any sort of Relational Database can be carved into multiple separate databases
These databases are completely isolated from each other
So we can have multiple apps with their own databases
Database Schema and Tables
Tables
A table represents a subject or event in an application
What does this mean?
Let's say we are building a E-Commerce application
We are going to have a table representing each part of the application
Users - Table for all users that have registered
Products - Tables for all the products that we sell
Purchases - Table for all the purchases made
All these tables will form a Relationship (Hence Relational Database)
If you think about it, a user will purchase a product and they will all be linked
Every purchase order has to be associated with a user account
A purchase order also has a list of products that the user wants to buy
It is very important that you think about these relationships before hand so you can design an efficient database
Columns vs Rows
A table is made up of columns and rows
Each Column represents a different attribute
Each Row represents a different entry in the table
DataTypes
Databases have datatypes just like any other programming language
In our case Postgres:
Why is this important?
When you create a Column within a table, you need to specify what kind of DataType you want to use
Primary Key
Is a column or group of columns that uniquely identifies each row in a table
Table can have one and only one primary key
The Primary key does not have to be the ID column always. It's up to you to decide which column uniquely defines each record
In the below example, since an email can only be registered once, the email column can also be used as the primary key
Unique Constraints
A UNIQUE constraint can be applied to any column to make sure every record has a unique value for that column
Primary Key has to be UNIQUE, but what happens when we have another row that needs to be unique?
That's where the Unique Constraints come in
In the above example we don't want duplicate names
Once we apply the Unique Constraint, SQL will check to make sure that the new name we are trying to enter doesn't already exist in the database
NULL Constraint
By default when adding a new entry to a database, any column can be left blank
When a column is left blank, it has a null value
If you need column to be properly filled in to create a new record, a
NOT NULLconstraint can be added to the column to ensure that the column is never left blank
Last updated