Rows, Columns and That
Alright, la.
I’m Mr Scouse Database Frog.
Today we’re learning
where computers keep stuff.
Loads of stuff.
Ribbit.
Rows!
Columns!
Find what you need!
That’s how a database does the deed!
A table’s got rows.
A table’s got columns.
Rows are records.
Columns are fields.
Easy. Name. Age.
Favourite biscuit. Very important data.
Now every row
needs a way to be identified.
That’s where a primary key comes in.
One value. One row.
No arguing.
An index helps us
find things faster. Instead of checking every single row...
we use a clever shortcut.
Like
knowing
which drawer the biscuits are in.
Now joins...
Joins let us combine
related information
from different tables.
Customers over here.
Orders over there. Join them together.
Hang on.
There’s that beat again.
Alright then.
Rows in the table,
columns in line,
primary key says “this record is mine.”
Index on the lookup,
find it real quick,
don’t scan the whole thing if the dataset’s thick.
Query says:
“Give me this, where that equals that.”
Database checks it,
sends the answer back.
JOIN two tables, match on the key,
customers and orders now connected properly.
SELECT! JOIN!
INDEX!
Transaction starts, all or none,
either every change works or the whole thing’s undone.
Commit if it’s good,
rollback if it’s bad,
keeps your data consistent
when things go a bit mad.
Normalize tables, don’t repeat everything,
constraints stop rubbish getting shoved in.
Unique means unique. Not “mostly unique.”
That’s not a thing,
lad. Back it up,
replicate, keep another copy safe,
because one broken disk shouldn’t ruin your day.
Rows!
Columns!
Keys in place!
Query the data at
database pace!
Ribbit.
That was tidy.
Anyway...
Transactions.
Very useful.
Indexes?
Make lookups faster.
Constraints?
Protect your data.
Backups?
Because
optimism
is not a recovery strategy.
Rows!
Columns!
Find what
you need! That’s how
a database does the deed!
I’m Mr Scouse Database Frog.
And remember: data is only useful if you
can find
it and trust it.
Ribbit.