Categories with Multiple Sub Categories

godwheel

Categories
CatID | CatName

SubCategories
SubCatID | SubCatName | CatID

Items
ItemID | ItemName

SubCatList
SubCatID | CatID | ItemID

But then I don't think this makes sense. What do you guys think?

I need to be able to give items many subcategories (which then belong to many categories). Is the correct way to do it?

Roger_Ramjet

Originally posted by godwheel

I need to be able to give items many subcategories (which then belong to many categories).

This is the bit that worries me.

many item : many subcat
many subcat : many cat

therefore

many item : many cat

Appears to be what you are saying. Some or all of these many:many relations need to be broken-down into a 1:many and a 1:1 relation. Which and how depends upon the data itself and the applications to which it will be put: 'Domain Knowledge' as it is called. I expect that you will need to invent 1 or 2 entities to rationalise this db scheme. Can't help any more without actually seeing the data sample.

godwheel

Ok thanks, I will post the exact thing I'm trying to work on with some examples. I figured I needed to break it down to sub-data, but I was worried about the queries to extract the data.

Thanks again, look forward to my post soon!

godwheel

OK HERE IT IS

To Revise

Stores can belong to many SubCategories.

EXAMPLES:
1. Marilyn's Clothing Depot belongs to the subcategory "Men" and the subcategory "Women" under the main category "Clothing."

Papaya Clothing belongs to the subcategory "Women" under the category "Clothing."

Categories can have multiple SubCategories.

EXAMPLES
1. Clothing can have Men, Women, Children,etc... as SubCategories.
2. Electronics can have Audio, Video, etc... as SubCategories.

SubCategories can have multiple Stores.

EXAMPLES:
1. The SubCategory "Men", can have the store "Mens Wearhouse" or "GAP".

I have the following TABLES in the Database (streamlined as to avoid having so much crap).

TABLE: Category
PK CatID
CatName

EXAMPLES: Clothing, Electronics, Entertainment

TABLE: SubCategory
PK SubCatID
SubCatName

EXAMPLES: Men,Women,Videos,Audio,Music,Theme Parks

TABLE: Stores
PK StoreID
StoreName

EXAMPLES: Papaya, Marilyn's Clothing, Best Buy, AudioFox, Club Space, Chuck-e-Cheese

There seems to be a many to many relationship from stores and subcategories, so I built a Table for that. But then I figured, and this is where I could be wrong, that a Category can have many SubCategories but a SubCategory can not belong to MANY Categories.

LINKING TABLE: SubCatStores
PF StoreID
PF SubCatID
PF CatID

Does this make sense? Do I need any other tables? If so, I will post the problem I'm having with the QUERY. Thanks and sorry for the long post 🙂

Roger_Ramjet

OK, if each store belongs to only 1 cat then you can store that in the stores table. For multiple subcats you will need the cross-ref table, but it only needs to contain the storeid and the subcatid.

To cross-ref cat and subcat use another table. That way if a user selected subcat Women they could be presented with a list of cats that have this as a subcat. And of course selecting a cat would lead to a list of subcats.

Once you have a cat and/or subcat then you can produce a list of stores.