Hi,

I am pretty new at this and I have a huge selection of books to categorise. Many of these books fall into multiple catagories. My task is to design the table structure.

Most of the information about the book I can place in one table, and I was thinking of creating separate tables for the category level ie cat0, cat1. The cats that I have for one book are as follows. Some have many more.

Classics > American
Classics > Fiction
Fiction > American
Fiction > General Fiction
Fiction > Short Stories


My thoughts are

book_tbl(book_id, book_title, isbn, etc.)
cat0_tbl(book_id, category_level0)
cat1_tbl(book_id, category_level0, category_level1)


But this will mean three tables all listing the books, and the cat tables will have multiple entries of the book. This will be ok to pull out which book to display against which category, but is there another (better) way?

Have I made some fundimental error here?

Thanks

Hello

My Proposal (without subcatories, but you can extend it)

book(book_id, book_title, etc.)
cat(cat_id, cat_name)

book_id_cat_id(book_id, cat_id)

You link the books to categories in a seperate table!

Walter

Edited my earlier post (wrong post)

Thank you Walter,

I'm getting confused here though.

Can you please explain why I need to create a third table. Why not just join directly?

If I create a joining table then it will only increase the amount of data I am holding/duplicating. Is there some speed advantage or anything else here?

secondly, If I need to create a second category, do I extend the table

book_id_cat_id(book_id, cat_id, cat1_id)

or do I create another table to join them ???

is there any source I can read up on this?


Thanks

You have books that can have more than one categories.
You have categories used for more than one book.
Thats a many-to-many relation:

Example
Book (id, name)
1; book1
2; book2

Cat (id, name)
1; Classic
2; Fiction
3; Short Story
4; American

Book_id_Cat_id(book_id, cat_id)
1; 1
1; 3
1; 4
2; 2
2; 4

=> book1: Classic; Short Story, Amercan
=> book2: Fiction; Short Story

The Join table will decrease the amount of data, because then
you don't dublicate your categories.

Walter

So to add a subcat, I would add the sub cat table and expand the join table

Example
Book (id, name)
1; book1
2; book2

Cat (id, name)
1; Classic
2; Fiction
3; Short Story
4; American

SubCat (id, name)
1; blue book
2; pink book
3; orange book
4; green book

Book_id_Cat_id(book_id, cat_id, subcat_id)
1; 1; 2
1; 1; 3
1; 1; 4
1; 3; 2
1; 3; 4
1; 4; 2
1; 4; 3
1; 4; 4
2; 2; 1
2; 2; 2
2; 2; 3
2; 2; 4
2; 4; 2

Is my understanding correct?

yes

Duh! I finally got the point.

I was trying to figure out why I need to create an extra joining table when I already had the data on hand.

I finally realised that using the id will allow me to duplicate the information with less chars, which will give me two things.

1. Less space used
2. Quicker searches because the search is only made on less chars


Thank you VERY much Walter for dragging me kicking and screaming through that

I now need to expand the above with shipping locations. because a book could be shipped to several different locations.

I think I need to add a location table and another join table.

Book (id, name)
1; book1
2; book2

Cat (id, name)
1; Classic
2; Fiction
3; Short Story
4; American

SubCat (id, name)
1; blue book
2; pink book
3; orange book
4; green book

ShippingLoc (id, location)
1; UK
2; France
3; Germany
4; USA

Book_id_Cat_id(book_id, cat_id, subcat_id)
1; 1; 2
1; 1; 3
1; 1; 4
1; 3; 2
1; 3; 4
1; 4; 2
1; 4; 3
1; 4; 4
2; 2; 1
2; 2; 2
2; 2; 3
2; 2; 4
2; 4; 2

Bookid_ShippingLoc (book_id, Shipping_id)
1; 1;
1; 2
2; 2
3; 2
3; 3

Could anyone tell me if I got it right? and if I didnt point me in the right direction please.

Thanks