A database is just a series of one or more tables. Each table is a separate and distinct entity. What makes a database “relational” is the ability to define relationships between one table and another. In some db systems (mssql), you can actually tell the system that there is a relationship. In others (mysql) you don’t have to define it. In either case, the nature of the relationships don’t change.
How relationships are defined
To setup a relationship, you just need a field on each table that matches. The field can be a number, a string, just about any field that isn’t a text field (cause they use pointers internally, that’s another lesson).
Student Table
- Student ID
- Social Security Number
- Student Name
Government Table
In the example above, there are two tables. One is a government table with your social sec number. The school table would be an example of your enrollment info at college. They assign their own student ID in an attempt to expose your ssn less, but they still use it to match up with govt information. Using this analogy you could match against the two tables using the ssn field. That would be the relational field. The key is to keep the info in the two fields matching.
One to One
A one to one relationship is like husband and wife. There is one in each table. A husbands table, and a wives table. Keep in mind that there are two tables involved, so it is very possible to have one in the first table with no corresponding item in the other. The key here is that when there is a match, it will only be one record on each side that matches. A more appropriate analogy would be with a table for men and a table for women and use a field to indicate the couple. Also, the previous example above using the ssn number is a good example of a one to one relationship.
Men
Women
In this example, you can have a list of men and women. When you want them to be a couple, set the couple id field to the same number on both sides. Then you can pull a report of married couples. We will assume that anyone with a couple id of 0 is single, and that anyone with an id > 0 is a couple. You could use null values here too for single, but to make things easy we will use 0 (some db systems require you to deal with nulls differently and I don’t want to confuse things).
To get a list of single men:
Select * from men where `coupleid`=0
Same for women, just change the table name:
select * from women where `coupleid` = 0
To get a list of couples, you do what we call a join. This creates a merge of the two tables based on your criteria. There right, left, and inner joins. We won’t worry about all the differences right now.
select men.manid, men.name, women.womenid., women.name, men.coupleid from men, women where men.coupleid=women.coupleid
This will look like the following:
1 | Bob Smith | 3 | Sandy Smith | 1
It will create a temporary table (recordset) in memory that you can use in your code (PHP?) the same way as any other query. The joined information contains the elements you asked for from both tables.
Notice that after the SELECT you use table.column notation to avoid any confusion since both tables may have similar column names. After the where statement is where we indicate how to join the tables. We specify that the coupleid column is the one that needs to match. The database will only return records when that field matches.
One To Many
One to many relationships are more common in databases. This is where you have one item on the left table and 0 or more items on the right side. Think of a parent. You can have zero or more children. You can’t just create 10 fields for children names, you will find people who have 14 or 15 children. And creating that many fields wastes massive space since the average couple will have 2.5 children. It is a better solution to build a new table with information for just one child per row.
Parent
Children
In this example, you have a parent with an ID. You then have another table that holds children. Each row is a child. To tell which ones are yours, you put your parentid in their parentid field. Then you can pull your kids out of the mess of children in the table. A little like picking your kid up from school.
To get your children (assuming your parentid is 1):
select * from children where ParentID=1
The key here is to make sure you know what your parent ID is. When writing code for this, you would need to track what parent you are looking at. In the example of a blog entry (parent) where you would want to view comments (children) you would want to track which blog entry you are viewing. This shouldn’t be a big issue as you are likely to have a url like the following when viewing the blog entry already:
/viewblog.php?blogid=1
The blogid=1 should be what you would use to grab comments and print them on the page:
select * from comments where blogid=1
Here it is in PHP code:
$sql = “SELECT * FROM comments WHERE blogid=” + $_GET["blogid"];
$rs = mysql_query($sql);
while ($record = mysql_fetch_assoc($rs)) {
print $record["comment"];
}
Many to Many
You can also do many to many relationships. This can get confusing though. Think plural wives and plural husbands at the same time. Generally this is just a really bad idea and shouldn’t be done unless there is no other way to represent the information. The linkage gets confusing and if you forget to update the linkage (relational fields) in your code somewhere it leads to trashed data. I am a firm believer in the KISS concept. The more you keep it simple, the fewer bugs you will introduce and the easier it will be to maintain later. Google many to many if you want for more information, but if you are reading this article and learning things, than stay away from many to many for now.
