13 Replies Latest reply on Sep 1, 2011 2:05 PM by AshleyEvans

    Help needed with tables and relationships

    AshleyEvans

      Title

      Help needed with tables and relationships

      Post

      Hi Guys,

      I am new to filemaker and am creating a database for my business. Our business involves selling cab glass for heavy machinery (jcb's etc)

      Every manufacturer and model of machine has it's own record in the "Machine Models" table. Every machine has only one cab, but one cab can fit many machines. Each cab is made up of up to 10 pieces of glass (items). One piece of glass (item) can fit multiple cabs.

      Before I go any further and import every item, could somebody tell me if this is an efficient way of organising the tables?

      http://s205.photobucket.com/albums/bb47/alderevans2007/?action=view&current=Tables.jpg

      Thank you very much!

        • 1. Re: Help needed with tables and relationships
          aammondd

          It sounds like you need to structure your data a bit differently you can do the basics of this with 4 tables

          Lets look at it like  this

          1 Each unique (by dimentsions and characteristics) Glass Inventory Item has an ID (One table of Glass Items with characteristic fields and a unique ID) Table has one record per Item

          2 Each machine has its own id (One table with Machine MFG. Info and ID) Table has one record per Machine

          3 Each machine has a number of Cab Postitions (One table with the MachineID and a number of Cab Positions) Table has one record per Machine/Position Combination

          4 Each Position may have one or more glass items that fit it (tint nontint etc quality etc) with MFG ID Position ID and Glass ID that fits Table has one record for each MFG/Position/GlassID that fits. (if there is only one glass item that fits the position then you can dispense with this table and add the item ID fields to the positions table but I suspect you might have 2-3 product types that fit  the same space)

          You can use relationships and portals to make the data entry and display for each of these easier in a variety of ways.

          Instead of having 10 or more fields on the Cab table you would have a related CabPositions table that listed the cab postions.

           

          • 2. Re: Help needed with tables and relationships
            AshleyEvans

            Thank you aammondd

            I think I understand. Do you mean something along these lines:



            Photobucket

            • 3. Re: Help needed with tables and relationships
              aammondd

              Very close The second table should be something like rather than seperate fields

              MACHINEID   POSITION POSITIONID(unseen)

              Machine1          1                 1

              Machine1          2                 2

              Machine1          3                 3

              Machine2         1                  4

               

              The third table would  keep the POSITIONID

              We do this because each table should have a unique serial id regardless of the data in the table.

              • 4. Re: Help needed with tables and relationships
                AshleyEvans

                Hey, Thanks again for your reply.

                I have to admit that I still cannot grasp this. If you take a look at this, could you tell me what would be wrong with it? I have a feeling that it's not a good thing for the Cabs table to have several fields that contain similar data. Is that where the problem is?

                One other thing, even though one cab may potentially have 3 Windscreens for example, we will streamline the items in the cab cab to a minimum number.

                Photobucket

                • 5. Re: Help needed with tables and relationships
                  aammondd

                  Correct there is a better structure

                  Lets for the moment add 2 tables CAB_POSITIONS

                  POSITION_ITEMS

                  The CAB_POSITIONS will create a POSITION_ID for each place you could place a glass on the Machine

                  It will have the fields

                  MACHINE_ID  this is the same as your machines table MACHINE_ID

                  POSITION_ID  this is an auto entered serial number unique to each record

                  MACHINE_POS this is simply a number that used for a human to understand the machines layout

                   

                  Now the POSITION ITEMS will have the following fields

                  POSITIONITEM_ID this is an auto enter serial number unique to each record

                  MACHINE_ID

                  POSITION_ID

                  ITEM_ID this will be the an ITEM_ID that comes from your ITEMS table.

                  Now this structure is better suited  to what you want to do.

                  Are you planning this to be multi-user?

                   

                   

                  • 6. Re: Help needed with tables and relationships
                    AshleyEvans

                    Hello Aammondd,

                    Thank you again for you reply. After getting my head back into this project I think I have grasped the concept of what you are saying. Woul this work better?

                     

                    Machine Table
                    Make Model Type CabIDfk
                    JCB 801 Excavator ECAB001
                    John Deere 7700 Tractor TCAB002

                     

                    Excavator Cabs Table
                    CabID Windscreenfk Lower Windscreenfk Left Upper Doorfk Left Lower Doorfk Right Front Quarterfk Right Rear Quarterfk Backlightfk
                    ECAB001 ECAB001_Win ECAB001_Lwin ECAB001_LD ECAB001_LD ECAB001_FQ ECAB001_RQ ECAB001_B

                     

                    Tractor Cabs Table
                    CabID Windscreenfk Left Lower Windscreenfk Right Lower Windscreenfk Left Doorfk Right Doorfk Left Rear Quarterfk Right Rear Quarterfk Backlightfk
                    TCAB001 TCAB001_Win TCAB001_LLW TCAB001_RLW TCAB001_LD TCAB001_RD TCAB001_LRQ TCAB001_RRQ TCAB001_B

                     

                    ECAB1
                    Position ID Item Code 1fk Item Code 2fk Item Code 3fk Item Code 4fk Item Code 5fk
                    ECAB001_Win P10001 P20789 P62236    
                    ECAB001_LWin P98661 P84775      
                    ECAB001_LUD P12334        
                    ECAB001_LLD P67789 P45667      
                    ECAB001_FQ P34567 P67899      
                    ECAB001_RQ P23445        
                    ECAB001_B P30098        

                     

                    TCAB1
                    Position ID Item Code 1fk Item Code 2fk Item Code 3fk Item Code 4fk Item Code 5fk
                    ECAB001_Win A13665 A45669 A89662 A89663  
                    ECAB001_LLW A10896        
                    ECAB001_LD A56998 A45338      
                    ECAB001_RD A10896 A71023 A456998    
                    ECAB001_LRQ A10655 P67899      
                    ECAB001_RRQ A96336 A45996      
                    ECAB001_B A10994        
                    • 7. Re: Help needed with tables and relationships
                      AshleyEvans

                      Would anyone else be able to help me with this? Thanks

                      • 8. Re: Help needed with tables and relationships
                        AshleyEvans

                         Or is this better?

                        • 9. Re: Help needed with tables and relationships
                          philmodjunk

                          Ashley,

                          I've been hoping Aammondd would respond, but he hasn't been active recently here...

                          I haven't looked over every single detail of this thread, but your latest post looks much better than the previous one. With the previous design, you could run into trouble should someone produce a redesigned cab with different glass locations as that might require adding new fields to your table and layouts to accomodate the change.

                          • 10. Re: Help needed with tables and relationships
                            AshleyEvans

                            Thanks for you reply. Smile

                             

                            Firstly, I have had some advice to split 'Make', 'Type' and 'Model Group' into seperate tables and I think this is a good idea. I think this is a good idea incase we ever need to store information e.g. notes, images regarding a make. Also, if a customer only ever purchased glass for say a jcb it may be easier to filter only JCB glass at this level. Or if we ever did a campaign for jcb glass, we could filter out all the jcb glass from here. I suspect it may be quicker to search if these entities are in their own table. Am I correct about this or am I talking rubbish?

                             

                            With regard to linking the Models to the Cabs (groups of glass), can you see anything drastically wrong with doing it this way? (below) I am thinking of not doing it the above way because in order to create a record for every cab position and then referece it to one or more items in the position items table will take forever (we have over 5000 pieces of glass). I aim to end up with just one glass item per postion and take care of any variations of say windscreens in the mode group table (for example, if the windscreen changes in 1999, then this will be a seperate model group and therefore will have only one windscreen associated with it)

                             

                            • 12. Re: Help needed with tables and relationships
                              philmodjunk

                              All looks very logical and straight forward to me. I can trace both the logic and relaitonships from table to table in the above image and they appear a text book example of implementing one to many and many to many relationships.

                              • 13. Re: Help needed with tables and relationships
                                AshleyEvans

                                Great stuff, thank you. Time to start loading in all the data. I have a feeling this may be the easy part, I've got a long way to go with layouts, scripts, calculations etc. Undecided