(part 1 understanding the schema)
In my first year of college I learnt about relational database models using mySQL to query a vintage auction site which we created. Since 2021 the technologies that have stayed relevant have changed so much, that when we started learning about schemas in my first week at the information lab, I wanted to understand how relational databases managed to stay so relevant.
Let's first understand what relational database models are:
Relational databases are comprised of schemas, which is a structure that defines what a database is allowed to contain (such as the constraints, datatypes) . They tend to have two distinct types tables, the fact table and the dimension table. The dimension tables, describes who or what, things that don't change often and hold mostly descriptive detail(attributes). Fact tables, which describe what happened, usually tied to a timestamp and referencing dimension tables through foreign keys rather than repeating their detail.
A relational database model is quite simply a database that organizes data into rows and columns, what makes them ‘relational’ is their ability to connect information across different tables without duplicating rows and columns. They do this mainly through two ways:
- A Primary Key
- a column (or sometimes a group of columns) that uniquely identifies each row in a database table
- Foreign Key
- A column in one table that references the primary key of another table. This establishes a clear relationship between the rows of both tables.
Sidenote: Sometimes secondary keys and composite keys are also used which are just alternate ways to uniquely identify a table. Secondary keys are another column that could uniquely identify a row but isn't the chosen primary key, for example an item's SKU code alongside its auto-generated 'Item_ID' they’re useful for lookups even though it's not the "official" identifier and composite keys are just a combination of columns to make a unique key for the table.
You might be thinking this sounds complicated, why can’t I just use a spreadsheet?
A spreadsheet is built to hold data, not to protect its integrity. As soon as the same piece of information, a person's email, an item's description, needs to appear in more than one place, a spreadsheet has no way to keep those copies in agreement with each other. Update one, forget the other nine hundred, and now the sheet is telling you two different things at once for the same thing.
A relational database is designed around a different goal entirely: every piece of information should live in exactly one place, and everything else should simply point to it. That's what primary and foreign keys are actually for, they're not just a way to organize tables, they ensure that a record only exists once and that every reference to it is pointing at something real. For example a bid can't reference a bidder who doesn't exist. An item can't be listed under a seller who was never entered into the system. None of that is enforced in a spreadsheet, there's nothing stopping a typo, a duplicate, or a broken reference from slipping in unnoticed.
So while a spreadsheet and a relational database might look similar at a glance, they're built for different jobs. A spreadsheet is a place to put data. A relational database is a system that keeps that data true and reliable as it grows, changes, and gets queried by other things, and becomes essential when dealing with datasets with thousands of rows.
In the next post, we'll get into exactly how relational databases maintain their data integrity, through the practice of normalization.