Where to Start: Learn About Relational Databases

You’re going to need somewhere to store your data, and you’re going to need to know how that data is stored.

Yes, AIs are really good at doing what you tell them to do.

“Hey AI, make me an app that can track butterflies.”

(Flutterbys is a much better name for butterflies, but that’s a story for a different post.)

And it will!

But here’s the thing. If you don’t understand how your data is stored, you can’t tell the AI what you actually want, and you won’t know if what it built is any good.

You don’t need to train your own language model to start building with AI. But you do need to understand the systems, data, and decisions behind the software you’re creating.

So let’s think about what our Flutterby app needs.

First, we need a place to save all your amazing users’ info: their name, user name, email (you need to tell your users when you see an Eastern Tiger Swallowtail, after all!) and password information, if you want them to sign in to your amazing Flutterby app.

And you need to store where you saw that amazing Eastern Tiger Swallowtail, right?

What if you want to let users post when they see one too?

Now you need to save that as well, and you, the app, and the AI all need to know how to “relate” your sighting to you and Jody’s sighting to Jody.

They’re different sightings, likely different butterflies, different places, and different people after all.

So now you are in the thick of it. The nitty gritty of database design.

Tables and Relationships

I am going to assume you’ve at the very least seen, and hopefully used, a spreadsheet.

Did I mention I think Excel is amazing?

Well, you can think of a database table as a sheet in Excel. You can think of columns as what we call fields, and rows as what we call rows (or records, if you want to sound fancy).

I know, row = rows, who’d thunk!

There’s one important difference, though. Unlike a spreadsheet, a relational database can enforce rules about how information connects, making it much harder to accidentally create inconsistent data.

Anyway, let’s create a Users table, but let’s stick with the spreadsheet example for now.

Use the top row as a header and give each column a name. That header is what we call the field name.

Let’s say we have five column headers:

• UserAccountNumber
• UserName
• UserFirstName
• UserEmail
• PasswordHash

The UserAccountNumber needs to be a unique value. We can’t have two users with the same account number.

The field that uniquely identifies each row is called the primary key.

Remember that name, you’re going to see it a lot.

We’ll have the database automatically assign each new user an account number, starting at 1001 in our example.

The same goes for UserEmail. It has to be unique too, unless you want to let the same user sign up as many times as they want, and we can’t have that because I have it on good authority that butterflies hate that!

Quick side note on passwords.

You never, ever store the actual password. You store a specially calculated value called a password hash, using a password-hashing algorithm designed for that purpose.

That’s a whole post of its own, but I didn’t want you walking away thinking it’s okay to save passwords as plain text.

It’s not.

The butterflies would hate that even more.

Here’s what our Users table looks like with a couple of users in it (I left the password column out to keep it tidy):

Okay, now we have the Users table designed.

The Magic: Relationships

We need a ButterflySpotted table next to track all those Eastern Tiger Swallowtails (Papilio glaucus, if you want to impress people at parties).

Sticking with Excel, let’s create the following headers.

By the way, this is the magic, so you might want to pay attention here.

• Column 1: AutomaticID
• Column 2: ButterflyName
• Column 3: UserAccountNumber
• Column 4: LocationSpotted
• Column 5: DateSpotted

The AutomaticID is built into the database design. We can set it up to start at 1 and go up by one for each new entry, so you never have to think about assigning the next number.

This is called an auto-increment field, and it’s the primary key for this table.

The true magic is the UserAccountNumber.

Why is it magic, you ask?

Well, that is what creates the relationship.

It points right back to the Users table, so now we know which user spotted which butterfly, and where and when.

A field that points to another table’s primary key is called a foreign key.

Here’s our ButterflySpotted table:

And here comes more magic.

Look at the ButterflySpotted table.

It has lots of UserAccountNumbers and ButterflyNames, but it has no idea what anybody’s first name is.

It only knows the UserAccountNumber, right?

But because we were smart and added the UserAccountNumber to the ButterflySpotted table, we can combine those two when we talk to the database.

We tell it: give me the UserFirstName and the ButterflyName wherever the Users table’s UserAccountNumber matches the ButterflySpotted table’s UserAccountNumber.

That’s called a JOIN.

Here’s what it looks like in SQL, which is the language you use to talk to the database:

SELECT Users.UserFirstName,
       ButterflySpotted.ButterflyName,
       ButterflySpotted.LocationSpotted
FROM ButterflySpotted
JOIN Users
  ON Users.UserAccountNumber =
     ButterflySpotted.UserAccountNumber;

And here’s what the database hands back:

Notice something?

Jody appears twice.

That’s not a mistake. Jody spotted two butterflies, so the database returns two sightings.

One user can have many sightings.

That’s called a one-to-many relationship.

And that’s another bit of database magic.

Don’t worry about memorizing the SQL. The AI will happily write SQL all day long.

What matters is that you understand what it’s doing.

And now we have a way to store, track, and retrieve the information our Flutterby app needs so far.

Go us!

What You Just Learned

• Table = a sheet in Excel, field = a column, row (or record) = a row.
• Primary key = the field that uniquely identifies each row.
• Auto-increment = the database generates the next number for you.
• Foreign key = a field that points to another table’s primary key, allowing the database to enforce a relationship.
• One-to-many relationship = one record in a table can be related to many records in another table.
• JOIN = asking the database to combine information from related tables.

Why This Matters for AI

Remember “Hey AI, make me an app that can track butterflies”?

Now you can say something like this instead:

“Create a Users table with an auto-increment primary key and a unique email, and a ButterflySpotted table with a foreign key back to Users.”

Same AI, way better instructions.

And when the AI hands you back its database design, you’ll actually know if it got it right.

That’s the point.

AI can write the code. You need to understand the decisions.

Try It Yourself

Here’s a little homework. Don’t worry, I won’t grade you.

Ask your favorite AI to design a database for an app that tracks something you love.

Maybe books, movies, restaurants, or even butterflies. I’m not judging.

Have it create two tables:

1. One for users.
2. One for the things those users have recorded.

Ask it to identify the primary keys, foreign keys, and relationships.

Then look at what it gives you.

Can you explain why it designed the tables that way?

If you can, congratulations. You’re already starting to think like a software engineer.

And if you can’t, ask the AI to explain it. That’s what it’s there for.

Just don’t blindly accept everything it tells you.

Next Time

Did you notice that ButterflyName is just typed in?

So what happens when Jody types “E. Tiger Swallowtale” and I type “Eastern Tiger Swallowtail”?

As far as the database knows, those are two completely different butterflies.

That’s not just a spelling problem. It’s a data design problem.

And fixing that is where databases get really interesting.

That’s Step 2.

Written by Troy Lee Yohn, with AI assistance for editing and technical review. The ideas, examples, questionable butterfly humor, and opinions about Excel are my own.