Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2007
    Posts
    10

    Question Relational Design for storing presently selected information

    I'm trying to store some information that describes which of a list is currently selected by the user.

    For example:
    Table: users
    users_id
    userName

    Table: collections (a collection can have only one name)
    collections_id
    collectionName

    Table: books (a book can have only one name)
    books_id
    bookName

    Table: users_collections (one user can have many collections, but a collection can have only one user)
    users_id
    collections_id

    Table: books_collections (a book can belong to many collections)
    books_id
    collections_id

    I want to store which collection and book a user is currently viewing, and am not sure how to properly store it:

    Option 1:
    New table called currentselections:
    users_id
    currentCollectionID
    currentBookID

    Option 2:
    Two new tables, currentBook & currentCollection:
    users_id
    currentcollectionID or currentBookID

    Option 1 seems attractive to me, but I can't seem to find any good advice on this in any database design resources.
    Last edited by TravisJ; 08-20-07 at 15:54. Reason: title mistake

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the difference between 1. and 2. is the difference between 3NF and 5NF (or is it 6NF?) and i don't think it matters

    can a user look at a book that isn't in a collection? can a user look at a collection but not at a book in that collection?

    it looks like user-book is 1-to-0-or-1, and user-collection is also 1-to-0-or-1, in other words, either is optional

    so i would use 1. and allow NULLs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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