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.
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.
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.
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.
What the Heck are Keys Already?
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.
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.
You just saw how you link tables using foreign keys. Now we’ll look at the different ways tables can interact.
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.
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.
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.
If you want to query two tables you must first merge their data with a JOIN Statement.
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.
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.
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.
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 < >.
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.
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!
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.
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.