2 Replies Latest reply on Nov 11, 2010 12:20 PM by FentonJones

    More efficient database design



      More efficient database design


      I think I may have bitten off more than I can chew for my first database project and I need some professional advice about the database I designed and created.  I have come to this conclusion based on the fact that I am beginning to learn how to write scripts and it seems like all my hard work into the design may be flawed.  Could someone here take a look at my file to see if there is something I could have done better in the design? 

      this database is for an oil service company.

      There is one customer with multiple services on multiple dates for install and removal using multiple invoices.  there is also a billing cycle associated with each piece of equipment that is billed out every month until the removal.   Can someone here please advise?  thanks


        • 1. Re: More efficient database design

          You did not get any answers. I would agree that you are in over your head. But then, we all are from time to time. A couple of suggestions. 

          First, I can't read your graph, for two reasons. It is too messy. This is not just you, it's almost all people who post their graphs. Learn to collapse/expand your Table Occurrences (TOs). There is a widget in the top right corner; or use the command keys, Cmd-T (Mac). You can do it for multiple at once. There are three states:
          1. All the way expanded, showing both the fields used in the relationship (top section), as well as all the other fields. This is practically useless, unless you're actively dragging fields, or really want to see all the fields for a moment.
          2. All the way collapsed, only showing the name of the TO. Very clean, but does not show enough for others to help you.
          3. Collapse to show only the fields used in the relationships. This is what we want to see.

          Decide what TO is at the top of your hierarchy. Put it on the left. String the others out to right, with each parent to the left of its children. Sometimes there are join tables, which sit between two parents; so the above is just a general method, with many exceptions; but it allows us to see the logic much better.

          There is a much better way to capture a picture of your graph. A screenshot at 75% is useless. I can't read any of the names. The way to do it, on a Mac anyway, is to click the Print button, at the bottom left of the Relationship Graph window. Then choose Save as PDF in the Print dialog; one page. This gives you a PDF of the whole graph, no matter how big. It can be zoomed, and remains clear.

          This is the Relationship Graph from my conversion of the FileMaker Business Productivity Kit (free), redone as one file (I hope they don't mind; but really this is how it should be done, IMHO). I also like to color all table occurrences of the same "base" table with the same color. This can be done by clicking on one, then using the widget at the bottom of the Relationship Graph to "Select tables with same source table". Then give them all a color.

          Notice that there are several different "Table Occurrence Groups" (TOGs), only connected within themselves. This is called the "anchor buoy" method. You may or may not need it. It is useful in complex projects, where you expect many more additions and operations to each basic area. It is less useful in tight hierarchies. But some separation is usually needed. When everything is connected, it can get messy in a hurry.

          So clean up your graph. And try and focus more on what you're asking. Just asking, "look at my graph and tell me what's wrong" may be OK, but better to ask about something specific within it.

          FileMaker's Business Productivity Kit, as one file

          • 2. Re: More efficient database design

            Also notice how each "anchor" is named with a short prefix, followed by 2 underscores. This identifies the TOG, is first on any of their connected TO names, and sorts that anchor to the top of its children. Grandchildren (etc.) are named with all their parents. That way they also sort alphabetically, and every path can be understood structually from its name. I am a "structural" namer, not a "functional" namer; though there are exceptions. When you're looking at a drop-down list of hundreds of names, related groups sorting alphabetically will help save your sanity.

            P.S. The graph is from version 2 of the BPK. They now have version 3. But it's very (too) similar to version 2, ie., still multiple files.