List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 8 2005 6:16pm
Subject:Re: Recursive category table problem (whatever you define it). Help!
View as plain text  
There are lots of ways to do this, from simple (lookup table and bridge 
table) to more complex (eg dynamic trees of nodes and edges). You'll 
likely have to experiment to find the solution that performs best in 
your context. Often simple is best. Here's a simple representation I 
used for a movie studio's categorised collection of its film reels.

Supposing your items are in a table named 'items', create a lookup table 
for all your 'categories', for example

    (cat_id int primary key,
     cat_desc char(20)

and a table called 'item_categories', child of items and of categories,  
for example

    (item_list_id int primary key,
     item_id referencing items.item_id,
     cat_id referencing categories.cat_id,
     order_num smallint

Then for each category which is applied to an item, add a row to 
item_categories setting cat_id to point at the chosen category, setting 
item_id to point at the item, and setting order_num to the desired value 
where order_num=1 means 'main' category, 2=sub, 3=sub-sub etc. The 
frontend app then has only to present a user interface for the 
item_categories table ensuring that the order_nums are sequential from 1 
and without dupes for any given cat_id.



symbulos partners wrote:

>Dear friend,
>I am classifying some items. The items will be stored in a large MySQL 
>database (probably InnoDB engine).  At storage time, we would like to append 
>a series of descriptive categories to the item, in order to facilitate 
>"friendly", very flexible searches later.
>For instance item A is in main category food, subcategory organic, 
>sub-subcategory vegetarian.
>Item B is category organic, sub-category food, sub-sub category baby.
>As you see the same category can be main category for one type of item, 
>subcategory for another item, sub-subcategory for another. 
>I have been working on this thing for a couple of days now, but I have not 
>been able to come out with any clever solution.
>I need 
>- good efficiency on multicategory searches
>- to store data in a relational table (if possible)
>- to update easily the list of categories (the table)
>- to address the problem of assigning a different status (main, sub, sub-sub 
>and so on) depending on the type of item to each category I put in the list.

No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.5 - Release Date: 2/3/2005

Recursive category table problem (whatever you define it). Help!symbulos partners8 Feb
  • Re: Recursive category table problem (whatever you define it). Help!Peter Brawley8 Feb