5 Replies Latest reply on Jun 22, 2011 1:01 PM by philmodjunk

    Database design question from an experienced DB person but a new FM user

    MichaelKaufman

      Title

      Database design question from an experienced DB person but a new FM user

      Post

      I have a fair amount of experience programming database apps (Powerbuilder, VB, Access), but I am just doing my first FM app.

      Lets say I have the following (partial) DB design:

      Investor                   Deal                           Investment

      --------                   -----------                    -----------

      pkInvestorID             pkDealID                   fkInvestorID                                

                                    strDealName               fkDealID

                                                                    numAmount

      I have layout for investors. I want to have a dropdown that shows the names of the deals the investor is involved in. In other systems I have used, I would create a query that would associate the three tables and create a result set that included the fields I needed to populate the dropdown (pkInvestorID, strDealName), but it looks like in FM, I have to include strDealName in the Investment table because that table is the only one directly associated with an InvestorID. But then the strDealName will have to be repeated for each Investment with that deal, which will make the DB larger and more difficult to maintain.

      Am I missing something? I am a big believer in DB normilization. Is that not really possible with FileMaker?

      Thanks for the help.