Adding Database Design Tables (DDT) to schema and layouts

Discussion created by eric on Oct 4, 2012
Latest reply on Oct 9, 2012 by taylorsharpe

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.)