Database basics: everything you need to know about databasing
by Lana Brindley, Nov 22, 2017
Databases underpin just about every software platform. Your favourite online store, that iPhone app you use every day, the system your HR uses to work out if you can take a day off or not, and just about every piece of tech you use to get your job done. Anything that relies on any kind of data needs to be able to organise that data so that it’s useful to people.
Databases are used to organise data in such a way that you can add to it, retrieve it, and—perhaps most importantly—draw conclusions from it by comparing different things.
Want to know why and how Amazon can recommend extra things to you, based on what other people have bought? That’s a database, doing what it does best: storing information, and working things out based on that information.
If you’re considering a new Codebots project (or any software project for that matter), there is a pretty good chance you will need to create a database for it. It could be just remembering all your users and their login details, or you might want to create an entire online shopping platform. Either way, a database will be the thing that does that for you.
Let's look closer at how databases store information.
Example: how to set up a home library
Imagine you want to set up a simple library, so you can lend books to your friends and keep track of who has what.
Step 1: A table
The first thing you will need is a table where you can store information about your friends: it will need to have a column for their name, their phone numbers so you can call them if you need your book back, and you might like to add their birthday or something like that in as well.
Step 2: Another table
Next, you’ll need a table to list all the books you have available for people to borrow. This will need to have columns for the title of the book, the author, and maybe some additional information, like whether it’s a hardback or a paperback, or even the ISBN (international standard book number) of the book.
Step 3: Relationships using keys
So now we have two tables (Friends and Books), which contain several columns of information (names, titles, etc). We need to be able to link these two columns together, so that we can form relationships between the data in the Friends table, and the data in the Books table, and be able to work out which friend has which book.
To make this a little easier, we need to be able to refer to each line in each table without having to write out the whole thing. We can do this by using unique IDs, or keys. The trick with keys is that they must be unique. In our example, we do this by taking the first three letters of the first two columns, followed by the last two numbers of the last column (this is called a concatenated key, just in case you like long words). You can do this in other ways, too, like using the ISBN, phone number, or barcodes (this is why ISBNs and barcodes were invented in the first place), or creating your own scheme. It doesn’t really matter how you do it, as long as every line in your database has a unique code.
So now we have this entry in our Friends table:
And we have this entry in our Books table:
Step 4: One more table
When Mary wants to borrow the Prodigal Son book, we need to create an entry linking together MarShe97 with KooPro12, so the easiest way to do this is to—you guessed it—create another table! Let’s call this one Borrowing:
As each friend takes a book, we add a new entry to our Borrowing table, along with the date they took the book. When they return it, we note the date down again. This time, our key is a combination of Mary’s key, and the Prodigal Son key.
What does this all mean?
So now we have a really interesting dataset, and we can ask the database to give us all sorts of information. Like how many books do I currently have loaned out, which of my friends have borrowed a book from me, what’s the most popular book that my friends borrow, or even draw interesting conclusions about correlations between book borrowing and birthdays, or maybe work out that the friends who live closer to you borrow more often. All of a sudden, you have access to a lot more information about your friends, your books, and how those things interact. The more data you have, the more interesting conclusions you can draw (and that’s why you should be wary about giving your data away, too!).
This was a really simple example, with very few items to track. But what happens when you’re dealing with hundreds of customers, thousands of products, stored in various sites around the country? And what if you then have to deal with something like changing the price of an item, or determining if something is out of stock? That’s a lot of tables, with a lot of keys, and a whole heap of data. And if you’re creating an online store, and you get your database wrong, it could cost you a lot of money. (Oh no! I updated the price here, but it didn’t update over there and now four thousand people have ordered free t-shirts!).
A codebot can't design a schema
Databases can be hard, and it’s important to get it right.
Our codebots can write you a database, but what they can’t do is help you design that database. The technical term for a database design is a database schema, and it requires a bit of human flair and creativity. Just like a picture, everyone will draw their schema in a slightly different way, and for that reason we can’t entrust it to the robots just yet.
What if I can't design a schema either?
Don’t despair, there are a number of ways you can get a great database schema, even without help from the machines. Try these ideas on for size:
- Outsource (or insource!): find someone who is great at creating database schemas, and give them whatever it takes to help you do it. Whether it’s a case of beer or a pay-cheque, they’ll probably relish the chance to show you their beautiful schema, and having these kinds of skills at the round table can be great for your team.
- Go do a course: Khan Academy, Coursera, or even an old fashioned TAFE or university course. Databases are fascinating, and once you have those skills, people might offer you cases of beer (or a pay-cheque!) to design theirs, too.
Of course, there’s also our Academy, which will help you learn how to take your shiny new database schema, and turn it into actual working software.