Re: Recursive category table problem (whatever you define it). Help!

[Peter Brawley]

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.

PB
-----
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.

Help!

 


--
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]