Database design question from an experienced DB person but a new FM user
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
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.