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,
and a table called 'item_categories', child of items and of categories,
(item_list_id int primary key,
item_id referencing items.item_id,
cat_id referencing categories.cat_id,
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:
>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,
>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.
>- 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