Relational Database Principles with Prisma and SQLite
Relational Database Basics
What's a Relational Database?
Think of a relational database as a highly organized digital filing cabinet. Instead of just stuffing documents into folders, you're arranging information into neat, interconnected spreadsheets. This structure makes it easy to find, update, and manage vast amounts of data without creating a mess.
The core purpose is to store data in a way that reveals the relationships between different pieces of information. For example, a business might want to connect its customers to the orders they've placed. A relational database makes this connection clear and reliable.
A relational database is a type of database that stores and organizes data points with defined relationships for easy access.
The Building Blocks
Every relational database is built from a few simple components: tables, rows, and columns. Let's break down what each one does.
Table
noun
A collection of related data organized in a grid format. Think of it as a single spreadsheet in a workbook. For instance, you might have one table for all your customers and another for all your products.
Column
noun
A vertical field in a table that contains a specific type of information for every record. If a table is a spreadsheet, a column is a single header, like 'First Name' or 'Email Address'.
Row
noun
A single, horizontal record in a table. It represents one complete item or entry. In a 'Customers' table, one row would contain all the information for a single customer.
Here's a simple Customers table to make this concrete:
| CustomerID | FirstName | LastName | |
|---|---|---|---|
| 101 | Alice | Smith | alice@email.com |
| 102 | Bob | Johnson | bob@email.com |
| 103 | Charlie | Brown | charlie@email.com |
In this example:
- The entire grid is the
Customerstable. CustomerID,FirstName,LastName, andEmailare the columns.- Each line of data, like the one for Alice Smith, is a row.
Connecting the Dots
The real power of relational databases comes from creating links between tables. We do this using special columns called keys.
A primary key is a column that uniquely identifies every row in a table. No two rows can have the same primary key value. Think of it like a Social Security Number for a person or a serial number for a product—it's a one-of-a-kind identifier.
In our Customers table, CustomerID is the primary key. Each customer has a unique ID, ensuring we can always find the exact person we're looking for.
Now, let's say we have another table for Orders:
| OrderID | CustomerID | OrderDate | Amount |
|---|---|---|---|
| 5001 | 102 | 2023-10-26 | $49.99 |
| 5002 | 101 | 2023-10-27 | $25.50 |
| 5003 | 102 | 2023-10-28 | $12.00 |
This table has its own primary key, OrderID. But notice the CustomerID column. It contains values that match the primary keys in the Customers table. This is how we link an order to the customer who made it.
A foreign key is a column in one table that refers to the primary key in another table. It's the 'glue' that connects your data.
Here, CustomerID in the Orders table is a foreign key. It creates a relationship, so we know that Bob Johnson (ID 102) placed two orders and Alice Smith (ID 101) placed one.
Keeping Data Trustworthy
Relationships are useful, but they only work if the data is accurate and consistent. This idea is called data integrity. Relational databases use rules, called constraints, to enforce it.
For example, a uniqueness constraint ensures that every value in a column is unique, which is essential for primary keys. An entity integrity constraint states that a primary key can't be empty (or NULL)—every row needs a unique identifier.
The most important rule for relationships is referential integrity. This constraint ensures that a foreign key value must match an existing primary key value in the other table. In our example, you couldn't add an order with a CustomerID of 999 if there's no customer with that ID in the Customers table. This prevents "orphan" records and keeps the data connections logical and clean.
These foundational concepts—tables, keys, relationships, and constraints—are the bedrock of relational databases. By structuring data this way, we create a system that is not only organized but also reliable and easy to work with.