6 Replies Latest reply on May 13, 2012 5:20 AM by petermorgan

    Initial Design


      Good design they say is where Function and Poetry meet, so would someone help me with the first verse.


      I am very aware that time spent initially, designing the structure of a DB, bearing in mind its function and use is so important, and to have a visual model in the mind or on paper before starting assembly. Therefore I would be most grateful if you could recommend a structure, flow and links at this early stage to prevent any suicidal thoughts on my part, many hundred hours down the line.


      Description: Sales / Order Catalogue


      Files: Products / Customers / Orders


      Display: Products: Four Product Groups - with approx. 8 product lines per group

      Display 1 page (Screen) for each Product line, with Product Details, prices and Quantity to order and to scroll to the next product sequentially, in strict order.


      To output a customer order, populated with quantities from the individual product page


      To enter the customer details and view/edit order details at this last step in the order process.



      I have looked for templates with a similar function, but have had no joy to date. Just want to start this task by setting off on the right foot as it were.


      Thanks Guys

        • 1. Re: Initial Design

          Hi Peter


          I am not sure what your experience level is… But I will approach this like you are new to FM.


          "Good design they say is where Function and Poetry meet" Absolutely right... And when it comes to the design of a FM database… It will have repercussions for the life of the product.


          First off... I think there are many ways to approach a project and there are many good developers here who may have different opinions than I do… So I would weigh that also when you decide to begin.


          I will break down some of my recommendations.  To fully explain a design approach would take a lot more than I could even begin to express in this thread.  So I will try to condense some things I consider important based on roughly 20 years of FM experience.


          1. When you say files... I am a huge advocate of the Single File approach.  Since version 7, FileMaker allows multiple Tables in a single File.  I will probably never build a multi-file solution again. I have posted this before... here are some of my reasons…


                    a. Vastly less repetitive developement work,  Table occurances only need to be built once.  Same thing with any Security settings and Custom Functions.  In addition… no need to worry about file references and you can change the file name with no consequences.


                    b. Easier in hosting environments… There are limitations on the amount of files FM Server can host… As well as a Cost-per-file on many/most hosting servers.


                    c. Also… ( this is more of an opinion based on approximately 20 years "I started with FM 2.1" using and developing with FM )… I believe there is a greater chance of File damage when a file looses connection with another solution file during data entry.  I "personally" feel a single file solution is more reliable than a multi-file solution.


                    d. Everything about working with FM-Go seems faster and cleaner with a Single File.


                    e. Easier to visualize as all calculations, relationships etc… are together in one file.


          2. The fewer Tables... the better...  If an entity has a lot in common with another entity I like to keep them in the same table and "Type" them differently.  This has numerous advantages when running reports and doing Import/Export.  An example would be "Customers" "Vendors" "Employees" "Banks"  etc... these all will have a similar field structure so I would keep these in a single table, Perhaps "Accounts" or "A"…  Also "Payments" "Charges" "Refunds" etc... These are all transaction items.  Additionally one could even keep the "Orders" "Sales/invoices" and even the line items in a single table.  This approach is a little bit more complicated during development but adds enormous power when it comes to reporting later.  Some people like to have a separate table for line items than the parent transactions this can be simpler to design… But in complex financial apps this can result in limitations.


          To me based on your data so far I would see a single file with 3 tables to start...


          Sales and Orders in one table.  possibly "Transactions" or "T"

          Products and Order Catalog in another table "Products_Services" or "P"

          Customers and any other person, place or company in another table called "Accounts" or "A"


          I like to keep my Table names really short… One or two letters all UPPERCASE ( I use all lowercase for table occurances )… This makes changing script parameters much easier and also makes the relational tree easier to keep organized.  I also ONLY build layouts based on the root table… NEVER on a table occurance.  In addition I ONLY base calculations from the root table context… Never from a related context.  The reason once again is more flexible reporting… As well as simplicity.  Sometimes you may need to add some more table occurances and calc fields to accommodate this but to me the trade-off is well worth it.  Also I am a fan of the Anchor and Buoy approach… All relationships will be occurances… I would never build a link from one Root table to another Root table.  Many people will dissagree with me on this… But I have seen many projects that start out with good intentions that wind up looking like a spider web down the line.


          Here are some tables I always also create "Main" or "M" mostly global fields for interfacing... "Defaults" or "D" used to hold program level preferences for the client... User level prefs can be done in the "Accounts" table.  Also... "Type" "Status" and "Category" or ( "Y", "S" & "C" ) … These tables allow you to organize and group the other tables as needed... By doing these as Tables instead of value lists you allow the client to use there own method of grouping.


          Field naming is important...

          When it comes to Field naming conventions everyone has there own take on this…  My approach ( as I am mainly a box product developer ) thus I need to be able to update the solution and allow the users to click a button to load all the data back in.  The most important thing to me is separating stored fields… For the stored fields I use no prefix… Example "Last_Name" or "Unit_Price"… The rest I add an underscore type prefix… A calc field might look like "_c_Total" a Global "_g_ID_Type" Summary "_s_"… The reason I do this is that FM does NOT allow you to sort the fields based on Type in the Export dialog… Because I am ONLY looking to export fields that have stored data… Having a "_" in front of the NOT to be exported fields sorts them the way I need.  This method makes it easier to select the fields for export script construction/editing.


          Also it is important to do most if not all of the relational linking on ID's not names.  Names change and are sometimes corrected… All tables which hold stored data… I include an "ID" field in.


          No words I can say will take the place of years of experience.  I hope this helps to get you started…



          • 2. Re: Initial Design

            Hi Mike,


            Thank you so much for such a detailed and prompt reply.  It is clear you enjoy your role as mentor and guide, as do I in other areas, particularly with my three grandsons. 


            You were right to assume a basic level of experience on my part, I was derelict in omitting my near tyro status when it comes to design and relationships, although I have been using FM for many years, I, like many others I suspect, have only modified existing templates to my particular needs.


            Thanks for picking me up on using the term 'File' and not 'Table' old habits die hard, and goes back to the 'Flat File' pre 'Relational' days of FM and I think the last time I used FM was Version 8, so I'm more than a little rusty … but enough excuses from me!


            I have read you email a number of times, and most of it is clear to me,  although I don't yet feel ready yet for my first Solo Flight.  I fully understand all your points about the suggested naming conventions and keeping 'Tables' to a minimum and linking on 'ID' etc, but, and perhaps I am worrying to much about this, I have not yet got to the point where I have a visual 'Model', a 'Schematic' if you like in my mind, let alone on paper, That's my first task.          I will conquer it eventually I hope, and if all else fail, I'll use my foppish charm on it


            Thanks again for your time and trouble in putting together such a detailed reply on convention naming, etc.  I assure you it is very much appreciated.


            An odd thought has just occurred to me … What would someone, reading this exchange of emails, in a hundred years time make of it, using their context and time.  Well, they might smirk about our technology and its challenges in this age, but more importantly they would pick up, I think, on the generosity of one stranger gladly and patiently passing on his knowledge to another, in the best Socratic tradition … finding the best solution to a problem together, unlike Plato, who liked to demolish the views and arguments of his students and interlocutors.


            At the very least, I will keep you abreast of my progress, or lack of it, if you don't mind or indeed make a second call on your well of knowledge of FM.









            • 3. Re: Initial Design

              Hi Peter


              Thanks for your kind words.  Please do keep me apprised of your progress.  I would be happy to answer any questions that I can.  You will also find many really nice people here with a wealth of experience in th FM area who also take the time to help others on the forum.


              If you have not worked much with FM since version 8 there are some items that came in at version 8.5 that are worth getting familiar with before you begin a project.  Variables and Script Parameters offer a new and more productive way of dealing with scripting.  Also the "Let" function offers great new ways to deal with complex calculations.  In version 9 we got conditional formatting which really helps tremendously with interface design.


              Good luck with your project,



              • 4. Re: Initial Design

                Hi Mike,


                May I ask for your advice again.  I will try to keep it brief and to the point.




                The Table Structure of the app I'm designing is as follows:


                          Customers          Products          Orders or (Invoices)          Product Line Items


                          The Product Catalogue consists of one product per page. Fields - (ProductID/Price/Order Qty)


                Question:     How do I populate the products ordered from the Product Page to a (Checkout) Basket for each Customer, ready to upload to a Server when on line and refresh the product page leaving the quantity field empty, ready for the next Customer Order.


                The Customer Name would only be added at the Checkout stage at he end after the basket had been populated with products and checked.





                I have spent four longs days trying to solve this, and feel distinctly ragged around the edges, so I think its time to shout help!!!


                If you strip out, environment, family history, culture and all other physiological reasons, they say the biggest factor in the cause of mental illness is thwarted ambition … well, I think I can agree with that.




                Sorry to bother you again.






                • 5. Re: Initial Design

                  Hi Peter


                  I think it might be a better approach to have the order quantity as part of the line items of an invoice/order.  The product id should be put into the line items along with the quantity, price and a session id (as the customer is not known at this point)...  I would write a script to create a customer account and an invoice at checkout then attach the lines to the invoice by inserting an invoice Id as well as the customer id.  Using the session id to round up the items.  Both the invoice and the line items should get the customer id.


                  I really don't think its a good idea to enter quantities into the product table.  This would prevent simultaneous access from multiple users.  No record can be accessed from more than one session at a time.   Users should never be entering any data into your product/catalog table.  You can create a global holder id for the session to relate to the line items if that helps your interface.  Global fields are session specific and can have different values in each session.  Entering data into a global field does not lock the record the way a stored field would.


                  Hope this helps,



                  • 6. Re: Initial Design

                    Hi Mike,


                    Thanks for your prompt reply.  I'm sorry for not being clear, butI never intended to enter  quantities into the Products Table, but simply to have the ability to enter the quantity/id for a product while still on the page of that product, to avoid constantly switching between the Product and Invoice page.  The only way I could think of was to use a  Portal for Invoice or Line Items, but that would show an accumulating list of products ordered, as you add each product and I don't have the available layout space.


                    Sorry to be so wooden about this, but I seem to be going round in circles.  I keep telling myself it's not rocket science, but I obviously lack the insight and skills at the design/relationship level, and I cant find any kind of template that mirrors these process steps.


                    Thank you again for you patience and advice.