4 Replies Latest reply on Jul 30, 2015 6:58 PM by laguna92651

    Recommended database structure

    laguna92651

      Title

      Recommended database structure

      Post

      I would like to do a financial portfolio analysis application.
      I will enter financial results every month or quarter for each year, for initially 10 stock funds. Stock funds may be added or removed over time.
      What is the best way to structure the tables? One table for all of the financial data or a table for each stock fund or some other structure?
      I will be graphing quarterly results and comparing results between stock funds over periods of time.
      Can anyone point me to an example of something similar?

      Financial_Portfolio.png

        • 1. Re: Recommended database structure
          philmodjunk

          Generally speaking, putting the data for each stock fund in a different table is not a good idea. Put it all in one table with a field that identifies the fund. In fact, you'd set up a second table with one record for each fund for recording data about each fund as a whole and link its ID to your table of transactions.

          • 2. Re: Recommended database structure
            laguna92651

            Thanks Phil, I was probably over thinking it.

            • 3. Re: Recommended database structure
              philmodjunk

              Also, don't get TOO hung up on the data model. Yes, a correct model is extremely important. But producing the right data model is often an iterative process--a process where you work out your basic data model, then start on the interface design for one section of the total project only to discover that you need to return to the data model and make modifications...

              Fortunately, FileMaker makes this fairly easy to do. But you might want to consider a process of frequent backups during your development process. While Undo is much better for rolling back layout changes than possible when the following thread was created, there still can be cases where you realize that you need to toss out a major change and revert to an earlier design option and that won't always be a layout change nor can even layout changes be indefinitely rolled back with Undo.

              So see the following thread for a scripted method that can save multiple back up copies of your file while you have it open for development: Saving Sequential Back Ups During Development

              • 4. Re: Recommended database structure
                laguna92651

                Thanks for the input and link, the development backup process has been an ongoing concern for me for awhile, I will definitely implement. Also the iterative approach makes a lot of sense for development, better to move ahead and develop than keep planning. Thanks again