Head First SQL

Chapter 4. Smart Table Design: Why be normal?

You’ve been creating tables without giving much thought to them. And that’s fine, they work. You can SELECT, INSERT, DELETE, and UPDATE with them. But as you get more data, you start seeing things you wish you’d done to make your WHERE clauses simpler. What you need is to make your tables more normal.

Two fishy tables

Jack and Mark both created tables to store information about record-setting fish. Mark’s table has columns for the species and common names of the fish, its weight, and where it was caught. It doesn’t include the names of the people who caught the fish.

Jack’s table has the common name and weight of the fish, but it also contains the first and last names of the people who caught them, and it breaks down the location into a column containing the name of the body of water where the fish was caught, and a separate state column.

Sharpen your pencil

Write a query for each table to find all records from New Jersey.

________________________________________________________________________

________________________________________________________________________

________________________________________________________________________

________________________________________________________________________

Sharpen your pencil Solution

Write a query for each table to find all records from New Jersey.

There are no Dumb Questions

Q:	Q: So Jack’s table is better than Mark’s?
A:	A: No. They’re different tables with different purposes. Mark will rarely need to search directly for a state because he only really cares about the species and common names of the record-breaking fish and how much they weighed. Jack, on the other hand, will need to search for states when he’s querying his data. That’s why his table has a separate column: to allow him to easily target states in his searches.
Q:	Q: Should we avoid LIKE when querying our tables? Is there something wrong with it?
A:	A: There’s nothing wrong with LIKE, but it can be difficult to use in your queries, and you risk getting results you don’t want. If your columns contain complicated information, LIKE isn’t specific enough to target precise data.
Q:	Q: Why are shorter queries better than longer ones?
A:	A: The simpler the query, the better. As your database grows, and as you add in new tables, your queries will get more complicated. If you start with the simplest possible query now, you’ll appreciate it later.
Q:	Q: So are you saying I should always have tiny bits of data in my columns?
A:	A: Not necessarily. As you’re starting to see with Mark’s and Jack’s tables, it depends on how you’ll use the data. For example, imagine a table listing cars for a mechanic and one for a car salesman. The mechanic might need precise information on each car, but the auto dealer might only need the car’s make, model, and VIN number.
Q:	Q: Suppose we had a street address. Why couldn’t we have one column with the entire address, then other columns that break it apart?
A:	A: While duplicating your data might seem like a good idea to you now, consider how much room on your hard drive it will take up when your database grows to an enormous size. And each time you duplicate your data, that’s one more clause in an UPDATE statement you’ll have to remember to add when your data changes. Let’s take a closer look at how to design your tables the best possible way for your use. How you’re going to use your data will affect how you set up your table.

Brain Power

SQL is the language used by relational databases. What do you think “relational” means in an SQL database?

A table is all about relationships

SQL is known as a Relational Database Management System, or RDBMS. Don’t bother memorizing it. We only care about the word RELATIONAL^[1]. All this means to you is that to design a killer table, you need to consider how the columns relate to each other to describe a thing.

The challenge is to describe the thing using columns in a way that makes getting the information out of it easy. This depends on what you need from the table, but there are some very broad steps you can follow when you’re creating a table.

1. Pick your thing, the one thing you want your table to describe.

   Note

   What’s the main thing you want your table to be about?

2. Make a list of the information you need to know about your one thing when you’re using the table.

   Note

   How will you use this table?

3. Using the list, break down the information about your thing into pieces you can use for organizing your table.

   Note

   How can you most easily query this table?

Exercise

Can you spot the columns in this sentence Mark the ichthyologist used to describe how he wants to select from his table? Fill in the column names.

Your turn. Write a sentence for Jack, the writer for Reel and Creel magazine, who uses his table to select details for his articles. Then draw arrows from each column to where it’s mentioned in the sentence.

Exercise Solution

Can you spot the columns in this sentence Mark the ichthyologist used to describe how he wants to select from his table? Fill in the column names.

Your turn. Write a sentence for Jack, the writer for Reel and Creel magazine, who uses his table to select details for his articles. Then draw arrows from each column to where it’s mentioned in the sentence.

We could, but we don’t need the data broken down to that level.

At least, not in this case. If Jack had been writing an article about the best places to go on vacation and catch a big fish, then he might have wanted the street number and name so readers could find accommodations nearby.

But Jack only needed location and state, so he only added as many columns as he needed to save space in his database. At that point, he decided his data was broken down enough—it is atomic.

Brain Power

What do you think the word atomic means in terms of SQL data?

Atomic data

What’s an atom? A little piece of information that can’t or shouldn’t be divided. It’s the same for your data. When it’s ATOMIC, that means that it’s been broken down into the smallest pieces of data that can’t or shouldn’t be divided.

30 minutes or it’s free

Consider a pizza delivery guy. To get to where he’s going, he just needs a street number and address in a single column. For his purposes, that’s atomic. He never needs to look for a single street number on its own.

In fact, if his data were broken into street number and street name, his queries would have to be longer and more complicated, making it take him longer to get the pizza to your front door.

Location, location, location

Now consider a realtor. He might want to have a separate column for the street number. He may want to query on a given street to see all the houses for sale by street number. For him, street number and street name are each atomic.

Atomic data and your tables

There are some questions you can ask to help you figure out what you need to put in your tables:

1. What is the one thing your table describes?

   Does your table describe clowns, cows, doughnuts, people?

   

2. How will you use the table to get at the one thing?

   Note

   Design your table to be easy to query!

   

3. Do your columns contain atomic data to make your queries short and to the point?

   

There are no Dumb Questions

Q:	Q: Aren’t atoms tiny, though? Shouldn’t I be breaking my data down into really tiny pieces?
A:	A: No. Making your data atomic means breaking it down into the smallest pieces that you need to create an efficient table, not just the smallest possible pieces you can. Don’t break down your data any more than you have to. if you don’t need extra columns, don’t add them just for the sake of it.
Q:	Q: How does atomic data help me?
A:	A: It helps you ensure that the data in your table is accurate. For example, if you have a column for street numbers, you can make sure that only numbers end up in that column. Atomic data also lets you perform queries more efficiently because the queries are easier to write and take a shorter amount of time to run, which adds up when you have a massive amount of data stored.

Sharpen your pencil

Here are the official rules of atomic data. For each rule, sketch out two hypothetical tables that violate each rule.

RULE 1: A column with atomic data can’t have several values of the same type of data in that column.

Note

Greg’s my_contacts column interests violates this rule.

RULE 2: A table with atomic data can’t have multiple columns with the same type of data.

Note

The easy_drinks table violates this rule.

Sharpen your pencil Solution

Here are the official rules of atomic data. For each rule, sketch out two hypothetical tables that violate each rule.

RULE 1: A column with atomic data can’t have several values of the same type of data in that column.

Note

Of course, your answers will differ, but here is one example:

RULE 2: A table with atomic data can’t have multiple columns with the same type of data.

Exercise

Now that you know the official rules and the three steps to making data atomic, take a look at each table from earlier in this book and explain why it is or isn’t atomic.

Greg’s table, NOT NULL appears in DESC _________________________________________________________

Donut rating table, Doughnut ask what your table can do for you... ___________________________________________________

Clown table, Clown tracking _________________________________________________________

Drink table, Exercise __________________________________________________________

Fish info, Two fishy tables ___________________________________________________________

Reasons to be normal

When your data consultancy takes off and you need to hire more SQL database designers, wouldn’t it be great if you didn’t need to waste hours explaining how your tables work?

Well, making your tables NORMAL means they follow some standard rules your new designers will understand. And the good news is, our tables with atomic data are halfway there.

Making your data atomic is the first step in creating a NORMAL table.

Exercise Solution

Now that you know the official rules and the three steps to making data atomic, take a look at each table from earlier in this book and explain why it is or isn’t atomic.

The benefits of normal tables

1. Normal tables won’t have duplicate data, which will reduce the size of your database.

   

2. With less data to search through, your queries will be faster.

   

   Because, even when your tables are tiny, it adds up. And tables grow. If you begin with a normalized table, you won’t have to go back and change your table when your queries go too slowly.

Clowns aren’t normal

Remember the clown table? Clown tracking has become a nationwide craze, and our old table isn’t going to cut it because the appearance and activities columns contain so much data. For our purposes, this table is not atomic.

Note

These two columns are really difficult to query because they contain so much data!

clown_info

name	last_seen	appearance	activities
Elsie	Cherry Hill Senior Center	F, red hair, green dress, huge feet	balloons, little car
Pickles	Jack Green’s party	M, orange hair, blue suit, huge feet	mime
Snuggles	Ball-Mart	F, yellow shirt, baggy blue pants	horn, umbrella
Mr. Hobo	Eric Gray’s Party	M, cigar, black hair, tiny hat	violin
Clarabelle	Belmont Senior Center	F, pink hair, huge flower, blue dress	yelling, dancing
Scooter	Oakland Hospital	M, blue hair, red suit, huge nose	balloons
Zippo	Millstone Mall	F, orange suit, baggy pants	singing
Babe	Earl’s Autos	F, all pink and sparkly	balancing, little car
Bonzo	Dickson Park	M, in drag, polka dotted dress	singing, dancing
Sniffles	Tracy’s	M, green and purple suit, pointy nose	climbing into tiny car

Sharpen your pencil

Let’s make the clown table more atomic. Assuming you need to search on data in the appearance and activities columns, as well as last_seen, write down some better choices for columns.

Answers in Sharpen your pencil.

Halfway to 1NF

Remember, our table is only about halfway normal when it’s got atomic data in it. When we’re completely normal we’ll be in the FIRST NORMAL FORM or 1NF.

To be 1NF, a table must follow these two rules:

Each row of data must contain atomic values.

Note

We already know how to do this.

Each row of data must have a unique identifier, known as a Primary Key.

Note

To make our tables completely normal, we need to give each record a Primary Key.

Brain Power

What types of columns do you think would make good Primary Keys?

PRIMARY KEY rules

The column in your table that will be your primary key has to be designated as such when you create the table. In a few pages, we’ll create a table and designate a primary key, but before that, let’s take a closer look at what a primary key is.

A primary key is a column in your table that makes each record unique.

The primary key is used to uniquely identify each record

Which means that the data in the primary key column can’t be repeated. Consider a table with the columns shown below. Do you think any of those would make good primary keys?

Watch it!

Take care using SSNs as the Primary Keys for your records.

With identity theft only increasing, people don’t want to give out SSNs—and with good reason. They’re too important to risk. Can you absolutely guarantee that your database is secure? If it’s not, all those SSNs can be stolen, along with your customers’ identities.

A primary key can’t be NULL

If it’s null, it can’t be unique because other records can also be NULL.

The primary key must be given a value when the record is inserted

When you insert a record without a primary key, you run the risk of ending up with a NULL primary key and duplicate rows in your table, which violates First Normal Form.

The primary key must be compact

A primary key should contain only the information it needs to to be unique and nothing extra.

The primary key values can’t be changed

If you could change the value of your key, you’d risk accidentally setting it to a value you already used. Remember, it has to remain unique.

Brain Power

Given all these rules, can you think of a good primary key to use in a table?

Look back through the tables in the book. Do any of them have a column that contains truly unique values?

The best primary key may be a new primary key.

When it comes to creating primary keys, your best bet may be to create a column that contains a unique number. Think of a table with people’s info, but with an additional column containing a number. In the example below, let’s call it ID.

If it weren’t for the ID column, the records for John Brown would be identical. But in this case, they’re actually two different people. The ID column makes these records unique. This table is in first normal form.

Geek Bits

There’s a big debate in the SQL world about using synthetic, or made-up, primary keys (like the ID column above) versus using natural keys—data that is already in the table (like a VIN number on a car or SSN number). We won’t take sides, but we will discuss primary keys in more detail in Chapter 7.

There are no Dumb Questions

Q:	Q: You said “first” normal form. Does that mean there’s a second normal form? Or a third?
A:	A: Yes, there are indeed second and third normal forms, each one adhering to increasingly rigid sets of rules. We’ll cover second and third normal form in Chapter 7.
Q:	Q: So we’ve changed our tables to have atomic values. Are any of them in 1NF yet?
A:	A: No. So far, not a single table we’ve created has a primary key, a unique value.
Q:	Q: The comments column in the doughnut table really doesn’t seem atomic to me. I mean, there’s no reasonable way to query that column easily.
A:	A: You’re absolutely correct. That field is not particularly atomic, but then our design of the table didn’t require it to be. If we wanted to restrict the comments to a specific predetermined set of words, that field could be atomic. But then it wouldn’t contain true, spontaneous comments.

Getting to NORMAL

It’s time to step back and normalize our tables. We need to make our data atomic and add primary keys. Creating a primary key is normally something we do when we write our CREATE TABLE code.

Brain Power

Do you remember how to add columns to an existing table?

Fixing Greg’s table

From what you’ve seen so far, this is how you’d have to fix Greg’s table:

Fixing Greg’s table Step 1: SELECT all of your data and save it somehow.

Fixing Greg’s table Step 2: Create a new normal table.

Fixing Greg’s table Step 3: INSERT all that old data into the new table, changing each row to match the new table structure.

So now you can drop your old table.

So, we know that Greg’s table isn’t perfect.

It’s not atomic and it has no primary key. But luckily for Greg, you don’t have to live with the old table, and you don’t have to dump your data.

We can add a primary key to Greg’s table and make the columns more atomic using just one new command. But first, let’s take a little trip to the past...

The CREATE TABLE we wrote

Greg needs a primary key, and after all the talk about atomic data, he realizes there are a few things he could do to make his columns more atomic. Before we look at how to fix the existing table, let’s look at how we could have created the table in the first place!

Here’s the table we created way back in Chapter 1.

Brain Power

But what if you don’t have your old CREATE TABLE printed anywhere? Can you think of some way to get at the code?

Show me the

What if you use the DESCRIBE my_contacts command to look at the code you used when you set up the table? You’ll see something that looks a lot like this:

But we really want to look at the CREATE code here, not the fields in the table, so we can figure out what we should have done at the very beginning without having to write the CREATE statement over again.

The statement SHOW CREATE_TABLE will return a CREATE TABLE statement that can exactly recreate our table, minus any data in it. This way, you can always see how the table you are looking at could be created. Try it:

SHOW CREATE TABLE my_contacts;

Time-saving command

Take a look at the code we used to create the table in The CREATE TABLE we wrote, and the code below that the SHOW CREATE TABLE my_contacts gives you. They aren’t identical, but if you paste the code below into a CREATE TABLE command, the end result will be the same. You don’t need to remove the backticks or data settings, but it’s neater if you do.

Although you could make the code neater (by removing the last line and backticks), you can just copy and paste it to create a table.

Note

Unless you’ve deleted the original table, you’ll have to give this one a new name.

The CREATE TABLE with a PRIMARY KEY

Here’s the code our SHOW CREATE TABLE my_contacts gave us. We removed the backticks and last line. At the top of the column list we added a contact_id column that we’re setting to NOT NULL, and at the bottom of the list, we’re add a line PRIMARY KEY, which we set to use our new contact_id column as the primary key.

There are no Dumb Questions

Q:	Q: So you say that the PRIMARY KEY can’t be NULL. What else keeps it from being duplicated?
A:	A: Basically, you do. When you INSERT values into your table, you’ll insert a value in the contact_id column that’s new each time. For example, the first INSERT statement will set contact_id to 1, the next contact_id will be 2, etc.
Q:	Q: That’s quite a pain to have to assign a new value to that PRIMARY KEY column each time I insert a new record. Isn’t there an easier way?
A:	A: There are two ways. One is using a column in your data that you know is unique as a primary key. We’ve mentioned that this is tricky (for example, the problem with using Social Security Numbers). The easy way is to create an entirely new column just to hold a unique value, such as contact_id on the facing page. You can tell your SQL software to automatically fill in a number for you using keywords. Turn the page for details.
Q:	Q: Can I use SHOW for anything else besides the CREATE command?
A:	A: You can use SHOW to display individual columns in your table: SHOW COLUMNS FROM tablename; This command will display all the columns in your table and their data type along with any other column-specific details. SHOW CREATE DATABASE databasename; Just like the SHOW CREATE table, you’ll get the command that would exactly recreate your database. SHOW INDEX FROM tablename; This command will display any columns that are indexed and what type of index they have. So far, the only index we’ve looked at are primary keys, but this command will become more useful as you learn more. And there’s one more command that’s VERY useful: SHOW WARNINGS; If you get a message on your console that your SQL command has caused warnings, type this to see the actual warnings. There are quite a few more, but those are the ones that are related to things we’ve done so far.
Q:	Q: So what’s up with that backtick character that shows up when I use a SHOW CREATE TABLE? Are you sure I don’t need it?
A:	A: It exists because sometimes your RDBMS might not be able to tell a column name is a column name. If you use the backticks around your column names, you can actually (although it’s a very bad idea) use a reserved SQL keyword as a column name. For example, suppose you wanted to name a column select for some bizarre reason. This column declaration wouldn’t work: select varchar(50) But this declaration would work: `select` varchar(50)
Q:	Q: What’s wrong with using keywords as column names, then?
A:	A: You’re allowed to, but it’s a bad idea. Imagine how confusing your queries would become, and the annoyance of typing those backticks when you can get away with not using them. Besides, select isn’t a very good column name; it tells you nothing about what data is in it.

1, 2, 3... auto incrementally

Adding the keyword AUTO_INCREMENT to our contact_id column makes our SQL software automatically fill that column with a value that starts on row 1 with a value of 1 and goes up in increments of 1.

What do you think will happen?

Better yet, try it out for yourself and see what happens.

Exercise

1. Write a CREATE TABLE statement below to store first and last names of people. Your table should have a primary key column with AUTO_INCREMENT and two other atomic columns.

   _____________________________________________________________________________

   _____________________________________________________________________________

   _____________________________________________________________________________

   _____________________________________________________________________________

2. Open your SQL terminal or GUI interface and run your CREATE TABLE statement.

3. Try out each of the INSERT statements below. Circle the ones that work.

   INSERT INTO your_table (id, first_name, last_name)
   VALUES (NULL, 'Marcia', 'Brady');
   
   INSERT INTO your_table (id, first_name, last_name)
   VALUES (1, 'Jan', 'Brady');
   
   INSERT INTO your_table
   VALUES ('', 'Bobby', 'Brady');
   
   INSERT INTO your_table (first_name, last_name)
   VALUES ('Cindy', 'Brady');
   
   INSERT INTO your_table (id, first_name, last_name)
   VALUES (99, 'Peter', 'Brady');

4. Did all the Bradys make it? Sketch your table and its contents after trying the INSERT statements

   your_table

   id	first_name	last_name
   Â	Â	Â
   Â	Â	Â
   Â	Â	Â
   Â	Â	Â

Exercise Solution

1. Write a CREATE TABLE statement below. Your table should have a primary key column with AUTO_INCREMENT and two other atomic columns.

   CREATE TABLE your_table
   (
   id INT NOT NULL AUTO_INCREMENT,
   first_name VARCHAR(20),
   last_name VARCHAR(30),
   PRIMARY KEY (id)
   );

2. Open your SQL terminal or GUI interface and run your CREATE TABLE statement.

3. Try out each of the INSERT statements below. Circle the ones that work.

   

4. Did all the Bradys make it? Sketch your table and its contents after trying the INSERT statements.

   

There are no Dumb Questions

Q:	Q: Why did the first query, the one with NULL for the id column, insert the row when id is NOT NULL?
A:	A: Even though it seems like it shouldn’t succeed, the AUTO_INCREMENT simply ignores the NULL. However, if it was not AUTO_INCREMENT, you would receive an error and it wouldn’t insert the row. Give it a try.

You won’t have to start over; instead, you can use an ALTER statement.

A table with data in it doesn’t have to be dumped, then dropped, then recreated. We can actually change an existing table. But to do that, we’re going to borrow the ALTER statement and some of its keywords from Chapter 5.

Adding a PRIMARY KEY to an existing table

Here’s the code to add an AUTO_INCREMENT primary key to Greg’s my_contacts table. (It’s a long command, so you’ll need to turn your book.)

Brain Power

Do you think that this will add values to the new contact_id column for records already in the table or only for newly inserted records? How can you check?

ALTER TABLE and add a PRIMARY KEY

Try the code yourself. Open your SQL terminal. USE the gregs_list database, and type in this command:

To see what happened to your table, try a SELECT * from my_contacts;

Will Greg get his phone number column? Turn to Chapter 5 to find out.

Your SQL Toolbox

You’ve got Chapter 4 under your belt. Look at all the new tools you’ve added to your toolbox now! For a complete list of tooltips in the book, see Appendix C.

Sharpen your pencil

Let’s make the clown table more atomic. Assuming you need to search on data in the appearance and activities columns, as well as last_seen, write down some better choices for columns.

There’s no definite correct answer here.

The best you can do is to pull out things like gender, shirt color, pant color, hat type, musical instrument, transportation, balloons (yes or no for values), singing (yes or no for values), dancing (yes or no for values).

To make this table atomic, you’ve got to get those multiple activities into separate columns, and those multiple appearance features separated out.

Bonus points if you wanted to separate out the location column into address, city, and state!

---

^[1] Some people think that RELATIONAL means multiple tables relating to each other. That’s not correct.