2 Replies Latest reply on May 12, 2016 9:08 AM by hidaz

    Relational database question

    hidaz

      I want to create a relational database but am struggling with the architecture. The scenario is this:

       

      I have 45 products that people can request printed literature for.

      Each product has 4 - 11 pieces of related literature.

      There’s a total of 33 pieces of literature.

      People may request literature for more than 1 product possibly resulting in multiple requests for the same piece of literature. In this case, only 1 instance of that piece of literature is sent.

      People requesting are circa 150 daily.

      Some literature changes frequently (monthly offers etc,)

       

      What I am trying to do is keep track of the literature I have on hand.

       

      Any help would be much appreciated.

        • 1. Re: Relational database question
          Mike_Mitchell

          hidaz wrote:

           

          I have 45 products that people can request printed literature for.

          Each product has 4 - 11 pieces of related literature.

          There’s a total of 33 pieces of literature.

           

          Since this math doesn't work directly (45 * 4 = 180, not 33), I'm going to assume each piece of literature can be associated with more than one product, just as one product is associated with more than one piece of literature. Therefore, you have what's known as a many-to-many relationship. The "standard" method for implementing such a situation is a join table, where each record corresponds to the unique combination of parent tables (in this case, product and literature).

           

          So I would start there:

           

          product

          literature

          productLiteratureJoin

           

          Then, we have this:

           

          People may request literature for more than 1 product possibly resulting in multiple requests for the same piece of literature. In this case, only 1 instance of that piece of literature is sent.

           

          This means you have something called a "request", and then you have "request items". IOW, a single request can have multiple items associated with it. So you add:

           

          request

          requestItem

           

          Then, you just script the addition of items from productLiteratureJoin into requestItem, removing the duplicates before filling the request.

           

          HTH


          Mike

          1 of 1 people found this helpful
          • 2. Re: Relational database question
            hidaz

            Thanks Mike.