Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90

    Unanswered: Relationship Design, simple db but I'm stuck

    Hello Everyone

    I haven't used Access for a few years, and unfortunately, my memory is not as good as it used to be! I'd really appreciate a point in the right direction, because I'm struggling with the design of this db.

    We are a book publishers, and need to a db to categorize our books. There are hundreds of books in (currently) 11 broad categories (Business, Finance, Marketing, etc). Each book will fall into one or several broad categories.

    Each category has many subcategories, up to about 20. Ie, 'Business' subcategories include 'Business Start Ups', and 'Business Finance'. These subcategories might fall into more than one broad category, eg, 'Business Finance' will appear under 'Business' and 'Finance'.

    Each book has a unique number, the ISBN. So the book would presumably be on the 'one' side, with the broad categories on the 'many'. But where do the sub categories sit? A further 'many' leading off from the broad categories?

    For the searches, the user needs to find

    a) books in a broad category
    b) books in a broad category with one or more subcategories
    c) books in more than one broad category, and more than one subcategory.

    Thanks for reading. If you would be so good as to answer, please pitch your answer at 'intermediate user' level.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider using a self referencing key

    categories
    cat PK
    parentcat FK to categories.cat

    parentcat should be nullable. a null parent indicates its a top category
    you will probably need nio more that 5 levels

    if you need multiple category types then use an intersection table

    bookcategories
    ISBN } PK
    category }

    if there is only one category per book then store the category inside the book row
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    Thanks for your reply - I remember you from 2010 when I was last on here, always helpful.

    Your solution seems rather difficult to me. I have never used a self-referencing key, and whilst I'm not against doing some net research on it, I think, in the end, I might not be able to understand and implement it myself. What about a many-to-many relationship, with a junction table in the middle?

    What I'd like to do is have a flat db with loads of radio buttons so different combinations of categories can be selected. Except that would be mad, but you get the idea.

  4. #4
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    Db has come on a lot, using a many-to-many relationship with an intersection table. Many 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
  •