Making a clean structured database

[Jalz]

Hi Guys,

Just curious, has anyone normalized (I use the term loosly here) their FileMaker database (i.e. found a way of separating calculation/subsummary fields with normal data fields). Im looking at creating a system from the ground up, so I've got no legacy issues here and been reading alot of data normalization as I've been working with other dbs - thing with FM is, you need the calculations stored in a field or subsummary data again stored within a field and before you know it, you have all these additional fields within your table. I know you need these additional fields as there is no other way of getting calculations/subsummaries etc working in fm (and I dont want to start using the webviewer to do calcs) - but has anyone managed to separate these 'additional' fields so they have a table with a fairly clean structure of just text/number/date/container fields whilst storing all the other calculations/subsummaries in another table and relating to them. Im just trying to bring new skills I have learn't in making databases more leaner - not sure whether we can truly separate these calc fields from the tables but I'd be interested in finding out if anyone has tried this in the past?

Thanks

[LaRetta]

Hi Jaltz,

In a way, conditional formatting and merge variables has allowed creation of calculations at the layout level (similar to Access) but the need still exists for summaries, aggregate calcs or calculations based upon related tables.

I do not believe, with the way FileMaker is designed, that true separation is possible (but watch, as soon as I say that ...). Check out what is possible using conditional formatting using Let() to write your calculations and using merge variables to display the results of those calculations. This technique can be used to display alternate text on a layout as well (or even display alternate field values) - it is not restricted to display or don't display.

Also turn regular fields into their boolean counterpart. It used to be that, if I wanted to display "Done" if a field had a date, I had to create a calculation but no more. Place another copy of your date field on the layout, turn off entry to the field, set the Data format (Inspector > Data tab bottom) to Boolean and type 'Done' in the Yes side and remove the No side. Merge fields also can be used to format field data differently instead of creating a calc to handle it. When you take advantage of these calculation-eliminators, your number of required calcs will drop significantly.

[Vaughan]

I've never had an issue with creating calculation fields in FMP. (It might be that I don't have a CS background and don't feel the need for data separation.) For me, calculation fields are fast and cheap and enable the database to *work things out for itself based on stuff it already knows* which is one of the principles of good design IMHO.

Yes I keep the calc field count down (another POGD is minimalism) but I also try to make the solution easy to understand and maintain (that might be from my farm boy background).

[Jalz]

Thanks Guys for taking out the time to reply. Laretta, I agree, conditional formatting and Merge variables have made it better - but there is still some things I think FM could improve, allowing us developers to create cleaner schema. Heres hoping for something like that in the very near future....?