MySQL & Statements in SQL Part 3
Thinking About your Tables
Here is the final installment for my SQL Tutorial, and today we are going to talk about:
- Atomic Data
- Normalized Tables
- Keys
- Working with Multiple Tables
- JOIN Statements
- UNION Statements
- With a few tips thrown in…
Atomic Data & Table Templating
As your database increases in size, you are going to want everything to be organized, so that it can perform your queries quickly. If your tables are set up properly, your database will be able to crank through hundreds of thousands of bits of data in seconds.
How do you know how to best set up your tables though? Just follow some simple rules:
- Every table should focus on describing just one thing. Ex. Customer Table would have name, age, location, contact information. It shouldn’t contain lists of anything such as interests, job history, past address, products purchased, etc.
- After you decide what one thing your table will describe, then decide what things you need to describe that thing. Refer to the customer example given in the last step.
- Write out all the ways to describe the thing and if any of those things requires multiple inputs, pull them out and create a new table for them. For example, a list of past employers.
Once your table values have been broken down, we refer to these values as being atomic. Be careful not to break them down to a point in which the data is harder to work with. It might make sense to create a different variable for the house number, street name, apartment number, etc.; but by doing so you may make your self more work? That decision is up to you? Look how different your queries would be:
SELECT house_number, street_name, apartment_number FROM customers; // Complicated
versus
SELECT street_address FROM customers;
Some additional rules to help you make your data atomic:
- Don’t have multiple columns with the same sort of information. Ex. If you wanted to include a employment history you should create job1, job2, job3 columns. Make a new table with that data instead.
- Don’t include multiple values in one cell. Ex. You shouldn’t create a cell named jobs and then give it the value: McDonalds, Radio Shack, Walmart,…
- Normalized Tables
By making your tables atomic, you are close to making them normalized. Normalized just means that the database is organized in a way that is considered standardized by professional SQL programmers. So if someone new needs to work with the tables they’ll be able to understand how to easily.
Another benefit to normalizing your tables is that your queries will run much quicker and the chance your database will be corrupted will go down. So, what are the rules for creating normalized tables:
- The tables and variables defined in them must be atomic
- Each row must have a Primary Key defined. Like your social security number identifies you, the Primary Key will identify your row.
- You also want to eliminate using the same values repeatedly in your columns. Ex. You wouldn’t want a column named instructors, in which you hand typed in their names each time. You instead, should create an instructor table and link to it’s key.
- Every variable in a table should directly relate to the primary key. Ex. You should create tables for all of your customers potential states, cities and zip codes, instead of including them in the main customer table. Then you would link them using foreign keys. Note: Many people think this last rule is overkill and can be ignored!
- No two columns should have a relationship in which when one changes another must also change in the same table. This is called a Dependency. Note: This is another rule that is sometimes ignored.
What the Heck are Keys Already?
Like I said before a key is a unique number that represents the rows in your tables. There are two types of keys:
Primary Keys
- Uniquely identifies a row or record
- Each Primary Key must be unique to the row
- Must be given a value when the row is created and that value can’t be NULL
- The original value can’t be changed
- It should be short
- It’s probably best to auto increment the value of the key
- Ex. cust_id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (cust_id);
Foreign Keys
- Used to make references to the Primary Key of another table
- Example: If we have a customer and city table. If the city table had a column which listed the unique primary key of all the customers, that Primary Key listing in the city table would be considered a Foreign Key.
- The Foreign Key can have a different name from the Primary Key name.
- The value of a Foreign Key can have the value of NULL.
- A Foreign Key doesn’t have to be unique
- Ex. name_of_fkey INT NOT NULL, FOREIGN KEY (name_of_fkey) REFERENCES name_of_table_pk (primary_id_name);
- When you define a Foreign Key you start with the name of the Foreign Key, then define all the attributes. Follow that with the FOREIGN KEY statement with the name of the Foreign Key in braces. Finally, after the REFERENCES statement, list the name of the table the Primary Key is located in and the Primary Keys name in that table.
Working with Multiple Tables
You just saw how you link tables using foreign keys. Now we’ll look at the different ways tables can interact.
One-to-One
In this One-to-One relationship there can only be one social security number per person. Hence, each social security number can be associated with one person. As well, one person in the other table only matches up with one social security number.
One-to-One relationships can be identified also in that the foreign keys never duplicate across all rows.
If you are confused by the One-to-One relationship it is understandable, because they are not often used. Most of the time if a value never repeats it should remain in the parent table being customer in this case. Just understand that in a One-to-One relationship, exactly one row in a parent table is related to exactly one row of a child table.
One-to-Many
When we are talking about One-to-Many relationships think about the table diagram here. If you had a list of customers chances are some of them would live in the same state. Hence, in the state column in the parent table, it would be common to see a duplication of states. In this example, each customer can only live in one state so their would only be one id used for each customer.
Just remember that, a One-to-Many relationship is one in which a record in the parent table can have many matching records in the child table, but a record in the child can only match one record in the parent. A customer can choose to live in any state, but they can only live in one at a time.
Many-to-Many
Many people can own many different products. In this example, you can see an example of a Many-to-Many relationship. This is a sign of a non-normalized database, by the way. How could you ever access this information:
If a customer buys more than one product, you will have multiple product id’s associated with each customer.
As well, you would have multiple customer id’s associated with each product.
You could create a table that just holds the foreign keys for both the products and the customers. This is called a Junction table. Then the Junction table loses normalization, but the other tables don’t.
JOIN Statements
If you want to query two tables you must first merge their data with a JOIN Statement.
The Full Join
You create a Full Join when multiple tables are listed. By joining two tables this way, each row from both tables is combined. Here is an example of how you could create a Full Join.
SELECT customers.*, products.* FROM customers, products;
When you perform a Full Join, you may be overwhelmed with data, so do so carefully. If you combined this Join with a where clause, you could decrease the number of results returned.
An Equi-join is one in which the WHERE clause limits the results to just cases in which two columns are equal. The Non-Equi-join, performs the opposite with a not equal sign < > inside of a WHERE clause.
The Straight Join
A Straight Join is used when you want to define the order in which the tables are joined. You tell the database server what order to use by listing them in the required order after the FROM statement. Ex:
SELECT STRAIGHT_JOIN … FROM table1, table2, table3…;
The Left Join
If you Left Join is used when you want to show rows in the left table that don’t match in your right table. Ex:
SELECT customers.*, products.*
FROM customers
LEFT JOIN products
ON customers.product = products.product_id;
This would return a table with all the columns in the customers table listed and all the columns in the products table being listed that have a matching column name with customers. You can also perform the opposite actions with a Right Join.
Inner Join
An Inner Join, is similar to a Full Join, except that you are able to add conditions that will limit and refine the results you receive. This is how you make an Inner Join:
SELECT us_presidents.first_name, pres_wives.first_name
FROM us_presidents
INNER JOIN
pres_wives
ON LENGTH us_presidents.pres_id = pres_wives.wife_id;
This would return a list of all Presidents and First Wives, that have a matching id number. This is referred to as an Equijoin, because it checks for equality of values as well.
There are other Inner Joins that check for inequality and the other performs tests on tables that have columns with the same name. To make a Non-Equijoin, just replace the equals sign in the comparison above with the not equals sign < >.
A Natural Join matches columns with the same column name. They look like this:
SELECT us_presidents.first_name, pres_wives.first_name
FROM us_presidents
NATURAL JOIN
pres_wives;
UNION Statements
With a UNION Statement you can also combine two or more tables. The UNION Statement combines all the values in the tables specified, cuts out the duplicates and serves up the results. This is an example:
SELECT first_name FROM us_presidents
UNION
SELECT first_name FROM pres_wives ORDER BY first_name;
This would return a list of all the US Presidents and First Wive’s in order.
If you want all of the values to be displayed including any duplicates, you can use the UNION ALL Statement.
That’s All Folk’s
Now you are a SQL Expert! I’ve described in these 3 tutorials pretty much everything you need to know to use SQL like a pro. Leave any questions you have below and in the next article I’ll wrap up my whole web design tutorial by showing you how to use PHP!
Till next time…
Think Tank
I’m confused. Why would it be necessary to create a whole new table to just list states? I don’t get why it’s bad for the same values to be used in different rows.
I wouldn’t say it is bad to do that, but instead that if you want your database to be atomic you should follow those rules.
Rules to help you make your data atomic:
1. Don’t have multiple columns with the same sort of information. Ex. If you wanted to include a employment history you should create job1, job2, job3 columns. Make a new table with that data instead.
2. Don’t include multiple values in one cell. Ex. You shouldn’t create a cell named jobs and then give it the value: McDonalds, Radio Shack, Walmart,…
3. Normalize the Tables
First Rule of Normalized Tables
1. Each column can only contain one value
2. You can’t have repeating columns
Second Rule of Normalized Tables
1. You can’t have repeating values in a field
Third Rule of Normalized Tables
1. Eliminate any fields from a table if they don’t directly relate to the primary key
More Here http://www.newthinktank.com/2010/12/web-design-and-programming-pt-15/