db theory advice multiple catagories per item

My co worker designed a database where retail items can be placed in multiple catagories. This seems odd to me.... In general, Isnt it more normal than not to be only one catarory for each item? For example, lets say I was selling a bowling ball with a picture of Mickey Mouse on it. I can then find this item in the "Mickey Mouse" catagory or in the "bowling ball" catagory but in the database the bowling ball has only one catagoryID. When I worked for a multi-million dollar corporate retail store , an item was listed once in only one catagroy. But i am sure items can be viewed

I know there isnt a single rule, I am just looking for a solution. How should the database sturucture be built with this in mind starting out with what is listed below?? Mabey an attributes table?

Items
ItemID ItemName CatagoryID

Catagories
CatagoryID CatagoryNameIf items can indeed be in more then one category, your coworker was probably right. That would be something like:

Items
ItemID ItemName

Catagories
CatagoryName

ItemCategoryMatrix
ItemID
CategoryName

If it can only be in one category, your solution would be better. You might want to have retail category and search category or something. It just depends what the business needs are.|||I guess my question would be this: if I asked 1000 retail stores/online ecommerce site how they built their database, which one would they use? What percent would use the matrix you described and what percent would use one catagory per item?

Let me throw in the fact that the co worker is not the best database programer so, what he did might, not be necessary......|||An awful lot depends on the industry/target market, and the complexity of the web site. If you have an auto-parts store, odds are good you can deal with one category per item. If you have Wal-Mart, you need to support multiple categories per item.

While any business can use a "single inheritance" tree with only one category per item (leaf node), as the business gets more complex and wants to reach more customers, the pressure increases to support more complex relationships using something like the link table "matrix" or something like it.

-PatP|||Your coworker is proactively thinking about functionality and scalability in the design. He sounds like a damn-fine database programmer to me. Tell 'im the blindman said so.|||and even if you're sure that each item belongs to only one category, it is still advisable to implement the many-to-many relationship table that derrick suggested

you can populate it with one-to-many data easily -- in fact, if you wish, you can even add a constraint to enforce the rule that an item can belong to only one category

but if it ever has to change, you simple allow multiple rows, and very little other changes (e.g. to queries) will be required

whereas with the initial design, with the category foreign key right in the item record, the change is extremely disruptive|||Yeah! Tell 'im Rudy the Canadian Dude says so too. We have international agreement now.

Good enough?

Helluva lot easier than calling up 1000 retail stores! As if THEY knew how to design databases...