Catalogue-ing books (2002)

[andrewgibsonsw]

Books have a Category and Sub-Category and I'm trying to store this information in a Book Database. How can I best store this information? I could have a related table with an ID number and two additional fields of Category and SubCategory. I could index these two so that the same Cat/ Sub-Cat pair wouldn't occur more than once. However this doesn't take advantage of the fact that most Sub-Categories only apply to a specific Category? Thanks, Andy.

[HansV]

If a significant number of subcategories belongs to more than one category, you have a many-to-many relationship between categories and subcategories. This is implemented by creating three tables: a categories table, a subcategories table that has no direct relationship to the categories table, and an intermediate table that lists the valid category/subcategory combinations. The intermediate table is linked to both the categories table and the subcategories table.

If the large majority of subcategories belongs to only one category, it makes sense to compromise a bit, and create only two tables, categories and subcategories, with a one-to-many relationship. Each record in the subcategories table contains a category ID. The combination of category ID and subcategory description is unique.
Most of the subcategory names in this table will be unique, but a small number will be repeated (if a subcategory belongs to more than one category). This is a violation of

[andrewgibsonsw]

Thank you.