Here are some basic terms you’ll need to use to work with databases.
- table– think of this as a spreadsheet or graph where each horizontal row is an individual record of some kind and each vertical column, called a field, is a different piece of information relating to that record. For example, if I had the fields Name, Age, and Hair Color, each row could describe a person, so I would have something like:
becuse they’re all in this table, I can later use this data to sort the people by their age from youngest to oldest (or oldest to youngest), alphabetize them by name, put them in groups according to their hair color, or sort by any other value in the table. Table names as well as field names must be one word, so we would make the Hair Color field HairColor, or something similar.
- primary key– in modern databases, you need to have what’s called a primary key. A primary key is one or more columns that mark each record as unique. Think of it this way: in our example above, they’re all different so it’s okay. But what if we add another Jane? There’s no way for the database to know that they are different unless there is a primary key. A primary key isn’t something a user needs to know- it’s just to tell the records apart, similar to numbering or lettering key points from a class in your notes. So we could just add a new field named id, and now we have:
and the database will always be able to know which Jane is which.
- datatypes– these are the different types of data (shocking) that your database can work with. Every field of every table must have a datatype or else the database doesn’t know what to do with the information you put into it. And nearly every database has a slightly different set of datatypes, so you’ll need to check in the database’s manual to know which exist. Below are some basic, common ones:
- int – integer – this is a whole number between -2147483648 and 2147483647 (but this range can also vary by which database you use.
- varchar – variable length characters – this is a group of letters, numbers, and other characters. You can decide how big to make it, but it can only be up to 255 characters. This is denoted as varchar(25) if you want the field to be 25 characters wide.
- double – this is a number with two decimal places, like money- 2.68
- float – this is a number with only one decimal place – 2.1
- text – this is a long field, like the varchar as to what you can put in it, but a text field is always the same size, whether you put one word or a zillion words, so it can be a space hog.
If you don’t mind your database running really slowly and badly, you could just have a bunch of text fields and I guess it would work. But don’t do that. With every field, decide what should go in it and how big it’ll need to be. For our example, we would probably do something like this:
You want to take into account that even though no one in your database has four letters in their name right now, you might eventually have someone in there with a longer name, like Maximillian or Nicodemus. If we were going to put last names in the same field, we’d want to make it even longer.
- relational database– most databases today are what’s called relational databases. They are called this because one or more fields in one table are “related,” or correspond, to fields in another table. For example, if we wanted to keep track of movies that people liked, we might put them in a text field at the end of the table we already have that keeps track of what their names, ages, and hair colors are. But what if we also wanted to keep track of other things like how many stars they would give to each movie or any comments they had about it? Then we would need to make a new table! We would leave the first one as it was before and add a new table called movies. In this table we’ll have the following fields: id, movie, stars, and comments. Movie will be the name of the movie, comments will be whatever they had to say about the movie, and stars will be the number of stars they would give the movie. The field id will be the id number of the person that likes the movie which corresponds to our original Name/Age/HairColor table! That way, each person can have a ton of movies and that first table can just stay the same. Since the id field in the first table and the id field in the movies table are related, this is a relational database.** Many databases allow you to tell the database which fields are related and then when you add a movie to the person whose id was 5 and there is no record for number 5 in our original table, the database says “Hold it! I don’t have a name or record or anything for person number 5!” and it make you add the person first.