1 2 Previous Next 16 Replies Latest reply on Apr 8, 2009 10:10 AM by philmodjunk

    Big picture question: Setting up the Structure.

    4hotshoez

      Title

      Big picture question: Setting up the Structure.

      Post

      I am new to FM, because Bento will not do all that I want to do.  I am trying to run a service business shoeing horses feet.  Horses live in stables with addresses and need to be scheduled and serviced regularly.  Horses have owners that pay the bill (invoice). Owners likely have more than one horse that may or may not live at the owners home. Each horse will have a record of past service (trim, shoe type and size, photos, measurments, notes, etc.), which should be reviewable for progress.  Horses also have vets and trainers that need to be contacted from time to time.  It seems that everything revolves around the horse, but the horse does not pay the bill.  What is the recommended structure to set this all up?  I have done the tutorial, but they do not give advice.

       

      Thanks

        • 1. Re: Big picture question: Setting up the Structure.
          philmodjunk
            

          To bad horses can't pay the bill :smileywink:

           

          Try listing things out on separate pieces of paper.

           

          List one would be all the human info you need.

          List two would be all the horse info

          List three billing information

          Etc.

           

          As you work with each list, look for cases where you have one set of information in one list that matches up with multiple sets of information in another list--that's what we call a relationship.

          Example: For a given Customer(human) record, you will have one or more Horse records. You'll need a relationship linking one customer record to many horse records.

          Likewise you can have multiple bills that you present to your customers for payment and these bills can list multiple services rendered at that time.

           

          Will that get you started?

           

          Once you've got a good design on paper, you can start defining tables, fields and relationships.

          • 2. Re: Big picture question: Setting up the Structure.
            4hotshoez
              

            I need help verifying the concept in this application.  Based upon your suggestion with some modification I would generate 3 tables from the 3 lists?  Might there be other tables to consider?  For example, after reviewing a sample file I noticed there was a Notes table.  What is the principle of knowing when to create a table or not?

             

            They would be called 1) People. 2) Horses. 3) Money.

             

            The People lists consists of contact information for:

            • Horse owners
            • Horse stables
            • Vets
            • Trainers
            • My suppliers for tools and consumables

            Also needed would be:

            • Notes on people
            • Maps
            • List of appointments
            • Record of payment
            • List of horses to owner/stable

             

            The horses list contains:

            • Photos
            • Descriptions of horse
            • Foot data
            • Shoeing data
            • Notes on horse
            • Service needed
            • Cost of service
            • History of service
            • Schedule of appointments
            • Owner
            • Vet
            • Horse location (stable)
            • Trainer

             

            The money list contains:

            • Service provided
            • Date of service
            • Cost of service
            • Record of payment
            • Horse id
            • owner id
            • Stable id
            • Notes of service
            • invoice id
            • expenses of business
            • Total income reports
            • Total expenses reports
            • Next appointments

             

            I assume that the bullet items would likely become fields? Is this correct?  I am sure that these are not all my fields but they make up the basics.

             

            The tutorials are weak on relationship examples.  Can you give me an example of one that might include all tables and what behavior I would expect from the links?

             

            Thanks, I know that this is a lot, but I think that I am beginning to see the light.

            • 3. Re: Big picture question: Setting up the Structure.
              comment_1
                

              A rough rule-of-thumb: when you see a "plural field", such as 'Photos' (implying one horse can have many photos), that's a sign that another table is required. Same with 'Owners': if one person can own many horses, and one horses can be owned by many people, then a "join table" of Ownerships is required between People and Horses.

               

              Tracking your work also needs to be done separately. I believe this is not much different from a standard invoicing solution, where each service  is equivalent to an invoice. The "customer" of the invoice is the horse being serviced. Every item of the provided service (labor or material) is recorded in a LineItems table, drawn from a Products table (this last one is basically your price list).

              • 4. Re: Big picture question: Setting up the Structure.
                4hotshoez
                  

                OK, Comment, this is great!

                 

                1) In regards to the multiple photos for each horse, is this done with portals? Some horses would have only one photo while others might have 20.

                 

                2) In regards to the invoicing, the horse may have more than one service item (product), while the horse owner needs just 1 invoice for multiple horses that may have multiple service each. How does this get built as far as tables and relations?  Please explain a LineItems table verses a Products table.

                 

                3) Are there recommended conventions for naming fields to keep things orderly in a table? For example, I have seen k_ID_PO and kf_ID_PO, which are fields in 2 tables of a purchase order.

                • What does k and kf signify?
                • Why the under bars rather than spaces?
                • Why are some CAPS and others not?
                • 5. Re: Big picture question: Setting up the Structure.
                  comment_1
                     1. Yes. Or, better put, it's done with a child table for the photos. A portal is just a layout device to show the child records - it's not part of your data structure.


                  2. Please note that I said that a service is LIKE an invoice. It would be neat and simple if each service record could also be an actual invoice, but since you say the owners may require a single invoice for multiple services, you will need a separate table (or group of tables) for the invoicing part.

                  Back to the services, the arrangement is quite simple:

                  First you make yourself a table listing all the various Products (or Items, if you prefer) you can provide, along with the price of each such item.

                  When an actual service is provided, you create a new Service record, and in a portal to LineItems (or ServiceItems, if you prefer) you create a record for each item included in the service (you do this by selecting the ID of the relevant item from the Items table). The price of the item is looked up into a "local" field in the ServiceItems table. The table arrangement looks like this:

                  Horses -< Services -< ServiceItems >- Items


                  3. Standards are a good thing - that's why there are so many of them...

                  For myself, I try not to use cryptic names such as kf_ID_PO, because I want to be able to read them, instead of having to decipher them. I'll have an auto-entered serial number field in every table, named after the table, e.g. HorseID in the Horses table, and ServiceID in the Services table. This would be the primary key of that table.

                  Then I'll have a similarly named field in any child table, e.g. a HorseID field in the Services table - this is the foreign key for the relationship back to the parent table of Horses.

                  Spaces in field names are allowed, but not recommended - mainly for SQL compatibility, but also because they are difficult to select. I use UpperCamelCase for field names, some prefer underscores.












                  • 6. Re: Big picture question: Setting up the Structure.
                    4hotshoez
                       My questions are beginning to grow faster than can be answered. The advice is great, but I think I need a book with illustrations, examples and explinations of DB concepts, principles and conventions.  Any book or web page suggestions that also use FMP?  Thanks.
                    • 7. Re: Big picture question: Setting up the Structure.
                      FentonJones
                        

                       

                      I have actually done something like this, for horses. One other thing you might want is a % of ownership field, in the OwnerHorses join table (or HorseOwners, whichever way you want to think about it). Because 2 owners do not necessarily own the horse equally. This % would be used for billing, each owner being billed for a service item proportionally to his % ownership of the horse.
                       
                      The way I did this was to enter the services as an "order." That is, just put the horse and the service needed. Then, at invoicing time, I ran a script that duplicated each invoice (and its line items) as needed for each owner of the horse, assigning the OwnerID and amount due, using the %. 

                      Another complication was that someone might bring in more than one horse at a time, each with different owners. Each owner would only expect line items for their horse. This was doable, because you know all the info when the script is duplicating/reassigning the line items to owners.

                      This would only be done after you were sure the order and treatments were ready to bill. Because, if you made a change after that, you would need to run the script again, to redo everything (I'd just delete the earlier ones and do them again; though I suppose you could be smarter, and more complex).

                      I suppose you might be able to do this without duplicating the invoices and line items, at least for printing purposes, by doing something like putting each owner into a global field, and filtering the line items thru the join table. But, on the other hand, using my method, it seems to me the final split invoices and line items are the reality of what is billed.

                      This is a heavy project for a beginner. But your list of requirements was well done, and shows that you have the basic ability.
                      • 8. Re: Big picture question: Setting up the Structure.
                        philmodjunk
                          

                        Glad to see my initial suggestion got the ball rolling in the right direction. Comment pitched right in with good advice.

                         

                        The advice is great, but I think I need a book with illustrations, examples and explinations of DB concepts, principles and conventions.  Any book or web page suggestions that also use FMP? 

                         

                        Have you looked at the tutorials offered through this web site? I haven't tried them myself being an old FMP hand dating back to FMP's flat file origins, but it might be worth a look.

                         

                        For books, you might try searching Amazon and then read the reader reviews to help select the option that's best for you.

                        • 9. Re: Big picture question: Setting up the Structure.
                          davidanders
                            

                          I have found this to be useful.

                          White Paper for FMP Novices

                          http://www.foundationdbs.com/downloads.html

                          • 10. Re: Big picture question: Setting up the Structure.
                            4hotshoez
                               FMP Database Design for Novices.pdf looks like a great resource. I just downloaded it and briefly skimmed through parts.  Thanks for the link.
                            • 11. Re: Big picture question: Setting up the Structure.
                              4hotshoez
                                

                               Have you looked at the tutorials offered through this web site? I haven't tried them myself being an old FMP hand dating back to FMP's flat file origins, but it might be worth a look.

                               

                              No, I have not seen them until now. I see one is 12 sessions for $99. Howeve, it is not clear that they are worth $8 a piece, and there is no sample file to test. It might be betterif I could buy one at a time. I did not see this option.  There is some free stuff that I will be trying.

                               

                              I have checked with Amazon before asking, but  I thought  some experienced trainers might know what books have been most helpful for people.

                              • 12. Re: Big picture question: Setting up the Structure.
                                comment_1
                                  

                                4hotshoez wrote:
                                FMP Database Design for Novices.pdf looks like a great resource.

                                It's a good read - just don't mistake it for a gospel.


                                • 13. Re: Big picture question: Setting up the Structure.
                                  4hotshoez
                                    

                                  PhilModJunk wrote:

                                  Glad to see my initial suggestion got the ball rolling in the right direction.


                                  I have 3 basic categories of People, Horses, and Money, I have a feeling that none of these are actual Tables. From the lists of words under these Categories (previous post) are going to be a mix of Tables, fields and Layouts.  Is this right?  What purpose do the 3 categories serve if they are not part of the actual structure?

                                   

                                  Next: Appointments for horses and owners need to been seen or linked to the owners page and to each horse listed for the appointment. It would be good to beable to see past, present and future appointments.  Are "Appointments" a table that can list all appointments in master list.  Then can this list be shown on a calendar?  With out getting too technical, how does this work in theory? I am trying to understand basic concepts without getting into coding, if that is possible.

                                  • 14. Re: Big picture question: Setting up the Structure.
                                    philmodjunk
                                      

                                    What purpose do the 3 categories serve if they are not part of the actual structure?

                                    The purpose was to help you to design a structure for your data. If you work with your lists and refine them as Comment and others have advised they can help you determine the tables, fields, layouts and relationships you'll need to build your data base.

                                     

                                    Next: Appointments for horses and owners need to been seen or linked to the owners page and to each horse listed for the appointment. It would be good to be able to see past, present and future appointments.  Are "Appointments" a table that can list all appointments in master list.  Then can this list be shown on a calendar?  With out getting too technical, how does this work in theory?

                                     

                                    Short answer: yes you can.

                                     

                                    Theory: Once you create a table that lists data such as appointment records, you can link it to as many tables by as many fields as you need to make your database work for you. Thus, you can set up 1 layout that lists all appointments in a master list. A second layout that uses a relationship to link all appointments for a specified owner and a third layout that uses a different relationship to link the appointments to a specific horse. You will also be able to take your master list and perform "finds" that can pull up all appointment records matching criteria you choose to specify. Thus you can pull up all appointment records for "Sea Biscuit" dated for today or later just by setting up the right criteria in a "find request." Using the same layout you could also find appointments for horses owned by "George Smith" and so on.

                                     

                                    There are many solutions that display appointment records in a calendar format so that can be done if the correct relationships and tables are set up.

                                     

                                    I am trying to understand basic concepts without getting into coding, if that is possible.

                                    Hope that helps. You are describing a solution that is sufficiently complex that you will likely need to create scripts (generate code) eventually to support your database.

                                     

                                    1 2 Previous Next