The Classic Example
A typical sample database structure example for handling categories.
. . . your computer will thank you!
The Typical Method Reviewed:
The classic method of building nested database categories is to use a single table with three fields as in the following example:
- Category ID
- Parent ID
- Category Name Text
This allows a single table to draw parent->child relationships to itself. It is simple, easy to understand and is widely used. At any point, a very simple SQL SELECT statement will get you either a given category's parent or children, which is pretty much all you need to build a working category database. For example, IF the root/home category where defined with an ID of Zero ("0"), the the following SQL Code would return all the first level category records, as they would necessarily all have a ParentID of 0. This is all you would need to get the display records for the first level of categories on a web site.
But this method fails to allow more than one connection, unless you add additional ParentID fields to the structure which is limiting and makes for cumbersome programing code as the additional fields may or may not be used. But still, this is doable and in some cases, might be the right answer.
A more flexible method is to use an intermediate link table to JOIN the records. In this way, possible parent and child connections are relatively infinite and the category table itself does not require the ParentID field to begin with. The SQL statement that connects the dots, so to speak, is, a tiny bit more complex, but still very efficient as the table's index's are INTEGER based, thus very small and can easily be fixed in the SQL server's memory.