Relational databases tie data together to reduce repetition and minimize storage used. Data can be stored compactly with intelligent relationships between tables and columns. When we start exploiting the features of a properly constructed relational database, the power of the SQL language is available to us. A relational database can be defined as
A database structure composed of more than one flat file (2-dimensional arrays) that can be transformed to form new combinations because of relations between the data in the records, in contrast to hierarchical and network database structures.
For example, in an earlier post I discussed the Members table of the content management system used at DanShope.com. The Members table contains all of the information provided by any given user, associated to a primary key, ID. Other tables such as the Projects table references this as MemberID so that we can tie specific projects to a particular member without repeating all of the member data in each project record.
In the same way, each project has its own ID that is referenced by the Pages table as ProjectID. Now we can have lots of pages that all point to one project, which points to one member. Any one member can have lots of projects that each contains lots of pages. This is an example of a one-to-many relationship, where the one MemberID is related to many projects, and each ProjectID is related to many pages.
Getting back to the MyLibrary example, we might also add another table called Members and a few columns to our existing tables, (ID, MemberID, SignedOut). The ID column should be configured as an Unsigned Integer (only positive whole values), Auto_Increment (each new record has an ID 1+ the last ID), and Primary Key (Each ID is used only once). To add a column to an existing table we use the following
mysql> ALTER TABLE Books ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;
I also specified this column as “NOT NULL” which means a value is always required. Since this is an auto_increment field, we WILL always have a value, automatically. MemberID will also be an unsigned integer so that it matches the ID column we will create for the Members table. However, it should not be auto_increment since we will need to specify this relation manually.
When we need to sign a book out of the library, the book record is updated to set the SignedOut field to true and the MemberID field to the ID of the member receiving the book. We can do this using the following syntax:
mysql> UPDATE Books SET MemberID = ‘1’, SignedOut =’true’ WHERE ID=’2’;
To use the UPDATE syntax we must already know which record we are updating using the WHERE clause. For other types of queries we can use nested or joined statements, such as getting the names of all the members who currently have books out.
mysql> SELECT Books.Title, Members.Name FROM Books LEFT JOIN Members ON Books.MemberID = Members.ID WHERE Books.SignedOut=’true’;
Here we are doing something pretty magical. I’m able to pull intermixed data from two seemingly unrelated tables, Members and Books. Indeed, by searching through the Books table for records that are signed out, pulling the associated MemberID, and comparing it to the ID in Members, I can get the Member’s name or any other pertinent information. You can see that if we added a date due column I could even test which members had books overdue and automatically compile a list for the librarian.
There are different types of JOINs based on what you are trying to accomplish and which SQL engine you are using. In this example we used a LEFT (INNER) join; there are also RIGHT (OUTER) joins. Left joins just mean that we will return every single unique record from the table listed leftmost (Books) and any records that match it from the right table (Members). If we did a RIGHT join we would get a list of all members whether they had books signed out or not.
Oh, how far we’ve come from our simple SELECT * FROM Books statement just moments ago. Now we are really starting to see the power of SQL and how properly constructed statements can do the heavy lifting of our data once we have it inserted.
Getting the data in place isn’t that difficult, and for a dedicated application you would hard code the statements instead of manually typing them every time. Even with these hardcoded statements, the data that doesn’t stay the same (anything after the equals sign) could remain dynamic. “Hardcoded” could either be a string or set of strings we store inside our application, or they could be SQL Prepared Statements, a really nifty feature we will discuss later. They both have the same end result (getting our data), but prepared statements have some neat benefits in the realms of security and database efficiency.
There’s a lot to be said about relational databases, indeed entire volumes have been published about the subject and it is still an area of constant research and refinement. I’ll post more examples later on that utilize data connections between tables that allow us to export valuable statistics and other data that would be time-consuming to correlate without the magic of SQL.
Labels: Databases, INSERT, JOIN, MySQL, relational databases, SELECT, SQL, UPDATE
Subscribe to DanShope.com