Table layout(s) for (sub)category-based item database
Posted: Tue Aug 24, 2004 10:55 am
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:
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:
My current database layout uses two tables, one for the categories, one for the items:
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
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 19An 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 7Code: 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 etcThanks a bunch!
Ryushe