Better Category Structure
An example, better database structure for category hierarchy.
. . . your computer will thank you!
Rather than adding additional ParentIDs to the category table, we create a LinkCat table to connect parent and child records. In this way, the number of connections or rather the number of logical paths that can lead to any part of the content's categories is, for all practical purposes, infinite. The link table also contains a sort value so that the list's sort can be other than the default alphabetic sort, if desired.
Category Table Fields:
- CatID
- CategoryName
- CategoryDesc
Link Table Fields:
- CatID
- ParentID
- SortDisplayByOrder
Now, for us to query the root categories on this site, the SQL statement would look something like this:
For the above to work, two things must be present in the category's data tables.
1) The category records themselves:
(see figure, right)
and
2). The LinkCat records that connect the category records to the appropriate Parent category.
(see figure, left)
Note that the CatID values correspond in each, which is what allows the category records, based on their ParentID as defined in the LinkCat table, to be found as a child of CatID=0. Since the LinkCat table can have many CatID=ParentID records, so to can any given Category have more than one parent and in effect, be in two places at once without duplicating data.
We have also added an additional description field to provide more detailed description than could be practically permitted by the navigation link's text by itself.
You might have already noticed that the title text for the the left hand navigation links and breadcrumb links at the top of this page, all provide popup text that shows a more detailed description. Creating concise descriptions for navigation can be challenging when trying to make sure that the titles used fit neatly on the page. The use of an additional title description makes this task a little easier while providing more detail to the user and better search engine support. While this is not strictly needed to provide good category structure, it is a good "Best Practices" example and taste of what we mean when we talk about practical application and common sense.
More on data and structure - >