5 Replies Latest reply on Oct 9, 2012 9:41 PM by taylorsharpe

    Adding Database Design Tables (DDT) to schema and layouts


      Below is my suggestion that, I think, addresses a mutitude of the database management suggestions, and would make FileMaker Pro so frightfuly powerful in the hands of developers that FMI would have to come up with a new name for FileMaker Pro Advanced.


      A developer (or at least Advanced developer) should be able to add Database Design Tables (DDT) to the Manage Database relationships graph. DDT tables and fields for database tables, fields, layouts, scripts, security, etc. would be predefined internally and would only need a developer to add it to the relationship graph in order to use it (much like an ODBC Data Source/ESS). If multiple DDTs are added to the relationship graph, FileMaker might even automatically suggest their relationships to each other appropriately.


      If there is any question what a DDT might look like or what fields it should include, consider the output of a Database Design Report, particularly the tables of an HTML report, whose cross-referenced hyperlinks indicate where DDT relationships belong. If DDT records were to include housekeeping fields like creation and modification time stamps and accounts, administrators would also have a means to audit database management. (Imagine if these DDTs were compatible with even more thorough audit tools like fmDataGaurd.)


      DDTs would also be similar to meta-data tables found in Oracle such as ALL_TABLES and ALL_TAB_COLUMNS, which provide extensive structural and statistical information about tables, columns, etc.


      DDTs would be powerful and superior alternatives to all kinds of database design and management features.

      DDTs would provide the kind of information Design Functions provide, only with greater details, with related design information from other design objects and with all the functionality, ease, and power of having data in FileMaker data tables.

      DDTs would provide all the kinds of information available from Database Design Reports (DDR), but unlike a DDR users and databases should be able to directly interact with that information in ordinary layouts, value lists, calculations, and scripts. Furthermore, if many of the DDT fields were editable (at least for Advanced developers), developers and databsae managers would make quantum leaps in their speed, ease, and power over database design and maintenance.


      For example, suppose a developer wanted to prefix all the calculation fields with "c_". He could simply find all the calculation fields (regardless of the tables to which they belong) in the "ALL_FIELDS" table and perform a calculated replace on those field names. (Scary, I know.)


      Another example: Suppose an administrator needs to quickly audit who has access to a Social Security field and to calculations and scripts that may might reveal its contents, and the admin must quickly correct all of several privilege sets with that access. Instead of navigating each of the privilege sets separately through Manage Security, an administrator could perform a find against the related privilege set DDTs to find all privilege sets with access to  that field. While reviewing the found set, the administrator would even be able to correct access as needed in the DDTs.


      Once a database can be used to inspect, use, and manipulate its own design, the managment development possibilities would be endless. (Think von Neumann.)

        • 1. Re: Adding Database Design Tables (DDT) to schema and layouts

          In the FileMaker 6 and before days, the predecessor to DDRs was done in FileMaker format and you could directly make changes in the output file, but those changes did not change the original file.  You have to go back to the original file to make changes to tables, scripts, layouts, etc. 


          There are several good FileMaker analysis tools that will take FileMaker DDRs and make them into easy to use FileMaker layouts with reports and summaries and graphs on problems and counts of various things. 


          What you are asking for basically is to use the DDR report to make changes in FileMaker schema and I do not see that happening because FileMaker is trying to keep things simple and by having two ways to alter schema, there can be a lot of issues such as what if two people are working on the same schema. 


          Check out some of the FileMaker optmimimzation tools that make use of the DDR.  Some of them are quite useful and let you know what things you need to fix. 

          • 2. Re: Adding Database Design Tables (DDT) to schema and layouts

            I remember the old fp5 DDR databases, and I was surprised they moved away from it instead of improving on it. I would have preferred that they had continued to produce the DDR tables, and then use an open method of generating HTML and XML from those tables.


            What I am basically asking for is at least a more direct table view to as many database managment elements as FileMaker can deliver. Get-functions are  weak by comparison, DDRs are not real-time enough, and I argue that even the developers of all those FileMaker analysis tools would prefer to read a familar meta-table model than a much-removed XML export.


            Think of all the general reasons we make databases out of data.

            Also, think where lies the bulk of the developer community's experience and strenghts: In the use, processing, interface design, scripting,  calculating, and integrating of FileMaker Pro tables and fields.

            Therefore, shouldn't we hope and shouldn't FMI support as much data as possible going into FileMaker Pro tables and fields?


            FileMaker Pro will achieve supreme database design functionality when advanced developers can redevelop any FileMaker Pro "File > Manage" interface in their very own layouts.

            • 3. Re: Adding Database Design Tables (DDT) to schema and layouts

              FileMaker is full of a bunch of compromises and I tend to be one who wishes more control at the Developer level.  But I understand that developers like myself are a small percentage of FileMaker users and recognize that Developer needs are frequently not put first in FileMaker priorities.  But hopefully FileMaker is listening and working on improvements to make us developers more productive and in control of our solutions. 

              • 4. Re: Adding Database Design Tables (DDT) to schema and layouts

                Yes. I think FMI understands that the more they help the developer, the more they help the end user. Version 12, seemed to be evidence of that. I don't think it needs to compromise that much—that's what FileMaker Pro Advanced is for—Advanced just needs to start living more up to its name.

                • 5. Re: Adding Database Design Tables (DDT) to schema and layouts

                  I agree and forsee a future where the Advanced client becomes more sophisticated and the regular client more of an end user or thin client type solution.  That is where things seem to be heading, but it is always hard to predict what FileMaker will do.