Table layout(s) for (sub)category-based item database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ryushe
Forum Newbie
Posts: 2
Joined: Tue Aug 24, 2004 10:55 am

Table layout(s) for (sub)category-based item database

Post by ryushe »

Hi,

I've been breaking my head over this one for the last few days. I need to build an item database where items can be assigned to a category, or a subcategory.
The standard and fairly easy way of representing this would be:

Code: Select all

Name                 ID    ParentID
 /- root catalogue    1     1
 |
 |- cars              2     1
 |  |- sportscars     3     2
 |  |  |- car 1       4     3
 |  |  \- car 2       5     3
 |  |
 |  |- sedan          6     2
 |  |  |- car 4       7     6
 |  |  \- car 5       8     6
 |  |
 |  \- 4x4            9     2
 |     |- car 6      10     9
 |     \- car 7      11     9
 |
 \- motorcycles      12     1
    |- 125cc        13    12
    |  |- mtr 1     14    13
    |  \- mtr 2     15    13
    |
    |- 250cc        16    12
    |  |- mtr 4     17    16
    |  \- mtr 5     18    16
    |
    \- 1000cc       19    12
       |- mtr 6     20    19
       \- mtr 7     21    19
Now, what my client wants/needs, is to be able to also display items such as car 1 (ID 4 with ParentID 3) under a another subcategory, for instance a subcategory called "GMC". An example would be a car which falls under 4x4's, made by GMC also showing up in the GMC category. To make things even more complicated, it is neccessary that under the GMC subcategory, the original type subcategories are also present.
An example without ID's or the Motorcycle category would be:

Code: Select all

/- root catalogue
 |
 \- cars
    |- sportscars
    |  |- car 1
    |  \- car 2
    |
    |- sedan
    |  |- car 4
    |  \- car 5
    |
    |- GMC
    |  |- sportscars
    |  |  \- car 1
    |  |- sedan
    |  |  \- car 5
    |  \- 4x4
    |     \- car 6
    |
    \- 4x4
      |- car 6
      \- car 7
My current database layout uses two tables, one for the categories, one for the items:

Code: Select all

TABLE categories
 ID        smallint(6)
 ParentID  smallint(6)
 Name      varchar(50)
 Desc      varchar(200)

TABLE items
 ID        smallint(6)
 CatID     smallint(6) (links to ID in categories)
 Name      varchar(100)
 Desc      varchar(250)
 etc etc
If any of you people gets what I mean, can you please help me out here with the table layout? Once I see the layout, I'll (hopefully) be able to get the data out structured code-wise (php4).

Thanks a bunch!

Ryushe
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

build a third table for linking between the two.
ryushe
Forum Newbie
Posts: 2
Joined: Tue Aug 24, 2004 10:55 am

Post by ryushe »

Sounds easy enough, although what should be in the third table then? The data on item(x) belonging to category A and category B ?
Alas, I'm no database designer, I usually just create the code around it.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

linkage table:
category_id
item_id
you can lose the CatID field in the items table then..

since you described a many-to-many relationship a linking table can often be the easiest and least duplication of data.. however, queries will take a little more time because of the added joining..
Post Reply