2 Replies Latest reply on Dec 12, 2011 2:22 AM by djcomidi

    linking to multiple subtype records

    djcomidi

      Title

      linking to multiple subtype records

      Post

      Hello everyone,

      To clearly describe my problem, I'll use a library analogy:
      As we all know a library has many shelfs.
      Suppose one shelf always contains 1 comic book, 1 novel an 1 dictionary.
      All three of these books have the same properties so they can be represented by 1 type: Book.

      My database at the moment has the following tables:
      BookType ( k_booktype_id, type_name ),
      Shelf ( k_shelf_id ),
      Book ( k_book_id, book_title ),
      Shelf_Books ( fk_shelf_id, fk_book_id, fk_booktype_id ).

      How can I enforce that when I create a new Shelf, automatically all Shelf_Books records for every BookType are created ?

      Thanks in advance,
      djcomidi

        • 1. Re: linking to multiple subtype records
          LaRetta_1

          Hi dj,

          "Suppose one shelf always contains 1 comic book, 1 novel an 1 dictionary."

          So you are saying you create a new shelf and you want to pre-fill the shelf with the 'three standard books'.  Is this correct?  The User will still have to select the three books.  Do you want to present the User with three lists (portals) where they select one book from each portal.  Portals will contain only one of each type of book (comic book, novel and dictionary).  They select the three books then script creates the shelf and the three Shelf_Books records as well.  Script can make sure three books are selected or it will not allow creation of the shelf.

          Please adjust my thinking if I am off base.  By the way, if a book can only be assigned to one BookType then you might want to assign the book type right in the Book table itself.  In this way, fk_booktype_id in Shelf_Books can be set up as an auto-enter from Book when a book is selected.  In truth, it isn't even needed in Shelf_Books table because it can be pulled from either Book or BookType but sometimes it is handy to have these indexed additional key fields in your join table.

          • 2. Re: linking to multiple subtype records
            djcomidi

            "Suppose one shelf always contains 1 comic book, 1 novel an 1 dictionary."
            So you are saying you create a new shelf and you want to pre-fill the shelf with the 'three standard books'.  Is this correct?

            Yes, this is correct.

            The User will still have to select the three books.  Do you want to present the User with three lists (portals) where they select one book from each portal.  Portals will contain only one of each type of book (comic book, novel and dictionary).  They select the three books then script creates the shelf and the three Shelf_Books records as well.  Script can make sure three books are selected or it will not allow creation of the shelf.

            A user doesn't have to create the different booktypes, they should already be there when shown/selected.
            My goal is to have 1 layout ( or 3, I'm not yet sure about that ) of Shelf in which I can select which book details I want to fill in.
            Something like "SELECT book_id WHERE book_type = <selected type> and shelf_id = <selected shelf>".

            By the way, if a book can only be assigned to one BookType then you might want to assign the book type right in the Book table itself.  In this way, fk_booktype_id in Shelf_Books can be set up as an auto-enter from Book when a book is selected.  In truth, it isn't even needed in Shelf_Books table because it can be pulled from either Book or BookType but sometimes it is handy to have these indexed additional key fields in your join table.

            That thought also crossed my mind. Both setups have valid arguments indeed.
            But in this case I'm only interested in "which books does this shelf have?", not "to which shelf does this book belong?"