3 Replies Latest reply on Nov 14, 2013 1:11 PM by philmodjunk

    Database Implentation Question

    projay

      Title

      Database Implentation Question

      Post

           Hello everyone, just nee a little input.

           My supplier("A") has provided me with there catalog in a excel spread sheet with item#, desc....etc..

           So my question is would i import all that information in to my current product table which is
           in my One database or...

           would I create another Database just for this catalog(which will be updated year to year) and ref it as an external source?

           Also if I have supplier "B" send me there catalog would I combine the 2 together or keep it separate?

           Any suggestions would be helpful...maybe you faced something similar to this.

           Thanks -J

            

        • 1. Re: Database Implentation Question
          philmodjunk
               

                    would I create another Database just for this catalog(which will be updated year to year) and ref it as an external source?

               You could, but there's no real need or advantage to you to have a separate file for that purpose. You can define a table for that same purpose within your database file for that purpose. Updating the table from year to year is much the same job whether the data is kept in a separate file or not. Of more interest is whether you need to add a date field to such a table and keep all records from all years in the table or just replace the old records with the new. Either method is pretty straightforward and the "best answer" depends on how you need to use this data in your solution.

               

                    Also if I have supplier "B" send me there catalog would I combine the 2 together or keep it separate?

               In most situations, you'll want all that data in one table with a field that identifies the supplier and that links these records to a record in a table of suppliers where you have one record for each supplier. But you may also find that the data from each supplier isn't structured in that excel file in exactly the same way, so you may need to have a separate table into which to import the data followed by a script that pulls the data into your combined table. That's a complication we could all do without but your suppliers may not leave you much choice since each is likely to have different data systems from which they are exporting the data to send to you.

                

          • 2. Re: Database Implentation Question
            projay

                 Thanks Phil for your reply.

                 So would the relationship be as below :  (or is there another way to do this?)

                 Invoice Line Catalog ID>====Catalog ID
                 Invoice Line Product ID>====Product ID 

                 -J

            • 3. Re: Database Implentation Question
              philmodjunk

                   I can't give you an answer. Did you mean this relationship?

                   Invoice Line::_fkcatalog ID = Catalog::__pkCatalogID AND
                   Invoice Line::_fkProductID = Catalog::__pkProductID

                   If so, then no, the relationship should just be:

                   Invoice Line::_fkProductID = Catalog::__pkProductID

                   as __pkProductID should uniquely identify a singe item in Catalog.

                   But I'm making dozens of assumptions about the possible structure and function of your database that may not be correct. If I change those assumptions, so do my answers.

                   For example, perhaps you have multiple suppliers that ship the exact same products to you for resale. In which case, unless you need to specify both supplier and product on the invoice. (Customer A gets widget A from supplier 1 and Customer B gets widget A from supplier 2...), you'd have a table structure where you have a table of products with one record for every product linked to a join table that then links to a supplier:

                   lineItems>-----Products-----<Product_Supplier>----Suppliers

                   Lineitems::_fkProductID = Products::__pkProductID

                   Products::__pkProductID = Product_Supplier::_fkProductID

                   Suppliers::__pkSupplierID = Product_Supplier::_fkSupplierID

                   And there are many other possible variations depending on your business model and the results that you need from your database.

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained