I'm trying to design a library catalogue in SQL, and the indexing of the different books is getting kind of complicated. For instance, a book could be said to be nonfiction as well as fall under the category of academics and subject of psychology. I created a table with a Category ID and Category Name but this is proving difficult. How do I properly index my tables so that my searches would be more streamlined and specific?
It's not clear to me exactly what you're after, so let's have a look at a couple of possibilities.
You'll probably want to set up a hierarchy of categories. Have a look at my previous answer The recursive relationship (21 February 2003). The hierarchy structure is often used for categories, and it will fit your requirements nicely, provided that each subcategory belongs to only one category.
You suggested category: academics and subject: psychology and I'm having a hard time seeing "subject" as a subcategory of "category." Perhaps they are different hierarchies altogether. I can easily imagine psychology as a subcategory of pop culture as opposed to academics, so it would then be under two different categories, which means the hierarchy model won't work. However, two separate hierarchies will work; each book would have two foreign keys, one to the category hierarchy, and another to the separate subject hierarchy.
You also suggested nonfiction but this is more of a "type" value rather than a relationship to another hierarchy. The types you might want to use to classify your resources could include fiction (books), non-fiction (books), magazines and periodicals, and audio/visual (tapes and CDs). These are not a hierarchy and would probably best be implemented as a lookup table.
Finally, with regard to your question about indexing and searching, those considerations are best left to the DBA (database administrator) who implements your logical design as physical database tables. If this is you, then you should simply postpone thinking about indexing and searching until you have established the exact structure(s) that you will need to properly classify your materials.
Please feel free to submit another question once your logical design is done.
Dig deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.