Thinking About your Tables
Here is the final installment for my SQL Tutorial, and today we are going to talk about:
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:
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:
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:
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
Foreign Keys
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/