multiple classifications

for the same article

[benihana]

i have articles that fall into several distinct catagories.

i would like to be able to classify them to fall under 1, 2 or X different catagories.

in my sql i have a table for all the nessecary info for the articles, including a varchar col called 'type', and a table for the different article types, containing an id and the name of the type.

do i need a lookup table? how do i get the relationship between articles and x amount of catagories?

again, thanks
ben

coopster, jatar, you there? ;)

[jamesa]

In your categories table include a column for the article ID (that would be what's called the foriegn key). That way you could select all the categories for a particular article with something like this: SELECT catname FROM categories WHERE articleID="x".

[benihana]

hi jamesa thanks for the response.

so my catagories table has 15 rows (=15 catagories), if i put another column on there for the article ids, how would i
store a number of different article ids for each catagory? comma sepearted?

thnks
ben

[Salsa]

I don't think it's necessary or desirable to have a record for each article in your types table. And you also don't need an additional lookup table. It appears to me that your type table is a lookup table. I'd keep the type_name column as it is, but consider making the type_id column with this scheme: 010000, 020000, 030000, etc. would be the top level categories. Subcategories of category one would be 010100, 010200… You can go as wide or deep as you want and make loops to create and manipulate the category assignments to your heart's content, keeping the articles neatly organized. Also consider adding a third column to the type table for keeping track of how many articles of each type there are. Then change the type column in your main table to the same name and data definition as type_id for cross reference.

You might also want to consider adding an article_order column to the articles table. On a menu, for example, I often have items appear by create_date DESC by default. Other times, sorting alphabetically is appropriate, but it doesn't always give the results you want. In any case it's often nice to be able to override other methods of sorting by assigning an order that items will appear in a particular category.