Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    18

    is my structure correct?

    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

  2. #2
    Join Date
    Feb 2004
    Location
    Siggenthal, Switzerland
    Posts
    19
    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

  3. #3
    Join Date
    Feb 2004
    Posts
    18

    Thumbs up

    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
    Last edited by bayder; 02-19-04 at 11:55.

  4. #4
    Join Date
    Feb 2004
    Location
    Siggenthal, Switzerland
    Posts
    19
    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

  5. #5
    Join Date
    Feb 2004
    Posts
    18
    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?

  6. #6
    Join Date
    Feb 2004
    Location
    Siggenthal, Switzerland
    Posts
    19
    yes

  7. #7
    Join Date
    Feb 2004
    Posts
    18

    Red face

    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

  8. #8
    Join Date
    Feb 2004
    Posts
    18
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •