3 Replies Latest reply on Oct 22, 2009 1:48 PM by philmodjunk

    Structure of database

    danherbert

      Title

      Structure of database

      Post

       

      I need some advice on how to structure my database to enable me to gain the following results:

       

      1) To be able to create prod_stock_cost records in a portal in the products layoutbut with it only allowing me to create records based on the relationship of the product to the supplier

      2) To create records in the prod_stock_cost table using a discount set in a new table which I can call supplier_category_discounts where I can set discounts on products in a particular category by a particular manufacturer which will then create records in the prod_stock_cost automatically for each product in the category at the set discount (normally a %)

       

      Been strugggling with this but need to sort out my master product file so I can use it for all our products!

       

      Thanks!

        • 1. Re: Structure of database
          philmodjunk
            

          I can only see your first image--the relationship graph. I see icons for additional pictures but they aren't appearing in my browser.

           

          I'm just responding to Question 1 for now:

           

          Just going by what I see in your relationship graph, it appears that any given product will come from one manufacturer but that manufacturer may have many suppliers. Thus, there's no way from the current table structure to link a specific Product Cost record to a specific Supplier record--there could be several different supplier records linked to the same manufacturer.

           

          You'll need some way to link your product stock cost records to supplier records if these are to be specific to only one supplier record.

           

          Make an new TO of suppliers and link it to your product stock cost table. I'd add a Suppliers_ID field to Product Stock Cost and use it to link to the new TO for Suppliers. I'd also remove the supp_name field from Prod_Stock_cost as I'll now be able to display the Suppliers_name field from suppliers whenever I need to see the supplier name for a given product stock cost record.

          • 2. Re: Structure of database
            danherbert
              

            Phil,

             

            Apologies, there was only one image, it was my first post and I couldn't figure out how to add an image - so amateurish I know!

             

            Thanks for your answer to the first question! This has really helped!

             

            Just want to figure out how in the products layout to make it so that in the portal that shows records from the prod_stock, how to only show the values for supp_id where the products manufacturer is linked to the supplier. For example if I can buy Van Damme from Direct Cables I only want to be able to select the supp_id for Direct Cables not all the suppliers for all the manufacturers.

             

             

            • 3. Re: Structure of database
              philmodjunk
                 This is called a "conditional value list". If you click the Advanced search link above, you'll find a number of threads where this has been discussed before. Read one or two of those threads and see if you can get it to work. If not, post back here and maybe we can take a closer look.