The first thing to note is that we have two tables here with a many to one relationship. “Many to one what?” What
this means is that there can be “many” links in “one” category, thus creating a “many to one” relationship. Other
types are “one to one” and “many to many”. To keep things simple we won’t be going into those. Another thing to note
is that they share a common bond “categoryID” – this “relates” each link to its respective category. Notice that
they have the exact same attributes – “tinyint(2) UNSIGNED NOT NULL” – which will optimize the JOIN we do on that
field later in this article. “Optimize … who needs to optimize – it’s only gonna have 15 links in it!” – well it’s
a good practice to get in, I know MySQL needs to have EXACT declarations for an optimized JOIN – other DB’s may not.
A good article on normalizing your tables can be found
here.
this means is that there can be “many” links in “one” category, thus creating a “many to one” relationship. Other
types are “one to one” and “many to many”. To keep things simple we won’t be going into those. Another thing to note
is that they share a common bond “categoryID” – this “relates” each link to its respective category. Notice that
they have the exact same attributes – “tinyint(2) UNSIGNED NOT NULL” – which will optimize the JOIN we do on that
field later in this article. “Optimize … who needs to optimize – it’s only gonna have 15 links in it!” – well it’s
a good practice to get in, I know MySQL needs to have EXACT declarations for an optimized JOIN – other DB’s may not.
A good article on normalizing your tables can be found
here.
I’ll leave how you enter the data into the tables up to you. I generally make a simple form to enter in cateogories
into link_categories and then use that table in my form for link_information – choosing a category each time I enter
a link and the categoryID being entered into the link_information table. So after we have some data entered into
our tables we have something like this:
into link_categories and then use that table in my form for link_information – choosing a category each time I enter
a link and the categoryID being entered into the link_information table. So after we have some data entered into
our tables we have something like this:
mysql> select * from link_categories; +------------+-----------------+ | categoryID | categoryName | +------------+-----------------+ | 1 | miester.org | | 2 | Linux | | 3 | miester's picks | | 4 | PHP | | 5 | My Desktop | | 6 | Mini Howto's | +------------+-----------------+ 6 rows in set (0.00 sec) mysql> select * from link_information; +--------+------------+-----------------+--------------------------+----------+----------------------+ | linkID | categoryID | linkTitle | linkURL | linkHits | linkInfo | +--------+------------+-----------------+--------------------------+----------+----------------------+ | 1 | 2 | Linux.com | http://www.linux.com | 69 | Info about link here | | 2 | 2 | Slackware Linux | http://www.slackware.com | 70 | Info about link here | | 3 | 1 | Home | http://www.miester.org | 377 | Info about link here | +--------+------------+-----------------+--------------------------+----------+----------------------+
“But I only want to show the title, link, and category name on my homepage!” – OK that’s where the JOIN comes into
play here.
mysql> SELECT I.linkTitle as title, I.linkURL as href, C.categoryName as category
-> FROM link_information as I, link_categories AS C
-> WHERE I.linkCategory=C.categoryID;
+-----------------+--------------------------+-------------+
| title | href | category |
+-----------------+--------------------------+-------------+
| Linux.com | http://www.linux.com | Linux |
| Slackware Linux | http://www.slackware.com | Linux |
| Home | http://www.miester.org | miester.org |
+-----------------+--------------------------+-------------+
3 rows in set (0.00 sec)
