Ask the Expert

Complicated categories

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?

    Requires Free Membership to View

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.


This was first published in August 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: