1 2 Previous Next 19 Replies Latest reply on Mar 15, 2010 9:47 PM by sackling

    Database creation Theory

    sackling

      Title

      Database creation Theory

      Post

      Hello,

       

      I work for a company that makes jewelry. They need to easily be able to order different stones, and gold/silver as well as the labor required per item number.

       

      I am very new to databases so I need some help just figuring out how to create this. Since there are a bunch of different stones to order depending on the product...Should I make a seperate field for each type of stone.. or should there be one filed called stones and each entry within it would be a different stone?

       

      the problem I see is that I have to type the style number in a field. so I would have the style number and it would need to be matched up with 7 different stones for example.

       

      Any assistance is greatly appreciated! Thanks,

       

      Rob

        • 1. Re: Database creation Theory
          sackling

          I have all the stones imported seperatly from an excel file. the fields are:

           

          Stone, Shape, Size, cost per carrat, Stones per strand, price per carrat, carat per stone, Price per stone.

           

          This all looks good. However I need to relate a certain quantity of stones to a seperate style number (representing a necklace for instance) How is this done?

           

           

          • 2. Re: Database creation Theory
            mrvodka

            You should have a table that has multiple records for what makes up a piece of jewelry. Lets take for example: Ring1.

             

            Well Ring1 could have the following Parts:

             

            2gm Platinum

            .5 ct Diamond

             

            etc etc.

             

            I dont know enough about the jewelry industry to make a great example but basically, an item has multiple parts that make it up with a specific quantity.

            The specific inofrmation about that part should be in a materials table.

             

            So your parts table should probably store the Item ID, Qty, and the Material ID.

            • 3. Re: Database creation Theory
              sackling

              Ok so.. If I understand correctly I will need 2 tables.

               

              Table 1)Materials table - which lists all Stones available to choose from and has fields - Stone, Shape, Size, cost per carrat, Stones per strand, price per carat, carat per stone, Price per stone

              Table 2)Parts table - All completed jewelry styles to choose from which has fieldsItem ID, Qty, and the Material ID

               

              What I dont understand is what happens when instead of a ring it is a necklace which requires many different kinds of stones. So instead of having just 1 material ID and 1 qty field there will need to be several?

              • 4. Re: Database creation Theory
                mrvodka

                No you need 3.

                 

                Item table  ( Necklace1, Necklace2, ring1, ring2, ring3, etc etc record )

                Parts table ( Item ID from Item table, Qty, and the Material ID from Material Table )

                Material table ( Information about each material )

                 

                Each Item can use multiple Materials. Each material can be used in multiple Items. This is called a many to many relationship and you can join them by using whats called a Join table; in this case named Parts.

                 

                Does that make sense?

                 

                Take for example for one type of necklace ( lets call it Necklace1), you need 10 grams of 18kt gold, 3 counts of .5 kt diamonds, and 4 emeralds.

                There would be 3 records in the Parts table. In your materials table would of course be the information about the materials such as 18kt gold, .5ct diamonds, emeralds. Obviously you could have different size diamonds, different kt gold, etc so they would be different records in the MAterials table..

                • 5. Re: Database creation Theory
                  sackling

                  I'm not understanding why there should be 3 records in the parts table. Sorry for the handholding I need here..

                   

                  Actually as I type this out it is starting to make more sense.

                   

                  so the 3 entries would all have the same Item ID. the material ID and qty would vary.

                   

                  That makes sense. now I need to figure out how to make the relationships between all the tables.

                   

                  • 6. Re: Database creation Theory
                    philmodjunk

                    This is a standard Invoicing type setup.

                     

                    In addition to the excellent advice from Mr Vodka, you might search this forum under the keyword "Invoice" for more examples. A few of the threads contain links to a demo file you can download.

                    • 7. Re: Database creation Theory
                      sackling

                      Thanks I will be searching for that. I feel like I am getting close here though. I am attaching an image of the table relations.

                       

                      Would it be possible to draw the lines for me or explain how they should be drawn?

                       

                       

                      Thanks Phil I will be looking for the invoice examples around here as well.

                      • 8. Re: Database creation Theory
                        mrvodka

                        Items table:

                        pkItemID

                        Description

                         

                         

                        Materials table:

                        pkMaterialID

                        Stone

                        Shape

                        etc etc

                         

                         

                        Parts

                        fkItemID

                        fkMaterialID

                        Qty

                        Price ( lookup )  // The only reason I included this is in case you wanted to add up all the prices for the items that make up the part.

                         

                         

                        Now connect:

                         

                        Items:: pkItemID = Parts::fkItemID

                        Materials:: pkMaterialID = Parts::fkMaterialID

                         

                         

                        Now you can either script it or use a portal ( will allow creation on in the relationship ) to add materials as parts of the item.

                         

                         

                         

                         

                        • 9. Re: Database creation Theory
                          sackling

                          Thank you I will try to work that out!

                           

                          out of curiosity is there a way to add in ID numbers automatically AFTER already importing data from excel? Or will I have to just redo the import and create the fields before?

                          • 10. Re: Database creation Theory
                            mrvodka

                            I did forget to mention that the ID fields should be set to auto-increment but I guess you figured that one out already.

                             

                            If you already did the import, then you can show all the records for that table, click into the field and then user Replace Field Contents which is under the Records menu. you will see an option to Replace with Serial numbers.

                            • 11. Re: Database creation Theory
                              sackling

                              Yep I figured something out on my own heh. Having trouble understanding the relation part still:

                               

                               

                              Items:: pkItemID = Parts::fkItemID

                              Materials:: pkMaterialID = Parts::fkMaterialID

                               

                               

                              Now you can either script it or use a portal ( will allow creation on in the relationship ) to add materials as parts of the item

                               

                              When you write Items::pkItemID

                               

                              does that mean to make a relationship between the whole Items table? I'm not sure how to draw that or if it is possible?

                              • 12. Re: Database creation Theory
                                sackling

                                Actually I think I may have it.. how does this look:

                                 

                                • 13. Re: Database creation Theory
                                  sackling

                                  I am not sure why they are appearing in a seperate box in the relationship table. in the invoice demo i downloaded there is no seperator like that...

                                   

                                  Also in the table I dont see any filed like Items::Item

                                  • 14. Re: Database creation Theory
                                    sackling

                                    I have it working now! Thanks so much for being so patient!

                                     

                                    The only thing I would like to add in is a drop down when selecting the FKITEMTID it would be nice to get a complete dropdown of the entire product list.

                                     

                                    I see this being done with the invoice demo. How is this done?

                                    1 2 Previous Next