[Article] Part 1: Upsizing Your Existing Solution—Schema & Data

By Bob Cusick
ClickWare, Inc.

I often get asked the question: "How big can our solution get before we need to migrate out of FileMaker Pro?" The answer is always: "...it depends." Sometimes a solution that is working "good enough" becomes a solution that's NOT "good enough" in terms of speed, reliability, responsiveness, etc. It has a lot to do with how the solution was designed, how it's being used, and what it's trying to accomplish. The purpose of this series of articles is to help you make an informed decision on if, when and how to upsize your existing FileMaker Pro solution to a Servoy/SQL solution.

If It Ain't Broken, Don't Fix It

My number one rule is: if it's not broken, don't fix it! If your current solution is working just fine (be it in FileMaker, 4D, Access, or whatever) - don't mess with it. If, however, you are not satisfied with one or more aspects of your solution - then it's time to take a closer look at what what's causing the problem, coming up with possible solutions within the existing program/database environment, and then, if all else fails, consider migrating the solution to a different database and user interface tool.

Step 1 - Analyzing What's Broken

The first step in the process - and probably the entire reason you're even reading this article - is because you are in some way less than happy with your current solution. The first thing you need to do is to identify EVERYTHING you're unhappy with - and be as specific as you can and try to quantify everything. The more specific you are - and the more you can quantify what's wrong - the better off you'll be later on in the decision process. For example - don't say "Too slow" - say "Too slow: sorting and summarizing 45,000 records for the XYZ report currently takes 12 minutes at peak hours with 25 concurrent users." Here's another couple of examples:

Rather Than Saying	Try This Instead
Updating to new versions is too time- consuming	Updating to new versions takes approximately 20 minutes per client and approximately 45 minutes per server. We have 300 clients and 12 servers for a total of approximately 103 man-hours. Have to re-import data on each new revision Importing data into each new revision takes approximately 60 minutes. We currently have to revise the solution approximately 10 times per month for a total of approx 10 man-hours per month.
Have to kick everyone off server to make calculation changes	We often have to add or correct calculation fields or global fields to respond to the requests for additional functionality from our users. Each time we make one of these changes we have to "kick off" all users who are connected to our solution. Our solution has 15 files and an average of 30 users with it open at any one time. It takes approximately 2 minutes to launch the entire solution - so each time we have to bring it down we're losing at least 1 hour of productivity (30 * 2 minutes) forcing everyone to re-open the solution and approximately 2 to 4 lost hours of productivity while we're making the change. We make approximately 5 of these types of changes per month for a total of 15-20 hours of lost productivity per month.
Integration with other SQL databases is difficult	It took approximately 20 hours to write an integration routine with our SQL Server that checks if data exists, and then pulls it down from the server and writes changed data back to the server. In addition, we had to engage the IT department's help in writing the SQL queries and in making the connection to the SQL database. We anticipate we will need 5 more of these types of read/write links for an approximate additional development time of 100+ hours.

Be as specific as you can. This will help you to evaluate new technologies and will help you quantify (and justify) migrating your solution.

Step 2 - Try To Fix What's Broken

There are some things that you can probably "fix" in your current solution to overcome some of the areas you're not happy with. Let's take our initial objection: "Too slow: sorting and summarizing 45,000 records for the XYZ report currently takes 12 minutes at peak hours with 25 concurrent users." You should have a look at the report and see if you can optimize it. Here's some questions you should ask:

• Does this report need all the summaries?
• Do we need it to be sub-summarized?
• Do we need it sorted?
• Can we store aggregations as number fields instead of using SUM fields or functions?
• Can we sort the data and import the import data into a temporary reporting file?

You'll find that about 50% of your objections (when it has to do with your solution and not the application) can be brought back from "unacceptable" to "acceptable" by making some changes to the way the solution works.

When you come up against objections on performance - you can ask yourself:

• Is it running on more powerful hardware than the minimum requirements?
• How fast is our LAN?
• Is there other network traffic that's causing a bottleneck?
• Are we using the right speed hard drives?
• Could we use a Citrix solution for VPN or WAN (or even LAN) clients?

Again, you'll probably find that 10%-50% of the time - you can solve your objection by making simple changes in hardware, operating systems, routers, etc.

When you come up against objections on the way the actual application (GUI or database) fundamentally works you need to try to get some answers. Start with the software vendor themselves - see if you can find out about upcoming releases, patches or updates that solve the problem(s) you're facing. Next, look to 3rd party developers - is there anything out there that solves the problems(s)? If the answer is "no" to both of the above THEN you have to "punt" and start looking for alternatives

Step 3 - Evaluating Alternatives

Once you've decided that the "grass is greener" - it's time to start trying to find some alternative technologies. If you're thinking about migrating your existing FileMaker Pro (or 4D or Access) solution - you have to evaluate two different sets of technologies: SQL-based database and graphical user interface building tools.

If you're in a corporate setting where there are already "blessed" SQL databases - or if you're trying to connect to your hosted shopping cart, etc. then you have half of the equation already solved for your. However, if you don't already have a SQL database - you've got some serious research ahead of you. There are dozens (if not hundreds) of SQL-based databases in the world: Oracle, MS SQL Server, Informix, Sybase, Firebird, OpenBase, FrontBase, mySQL, mSQL, Postgres SQL, etc., etc. When you drill down through all the various features of the various database vendors in all comes down to this: SQL databases do one thing and ONLY one thing; store data and retrieve, sort and summarize it very quickly. There are lots of other features that vary by vendor - but MOST SQL databases also support backup, replication, stored procedures (internal or external), BLOBS (Binary Large Objects), etc. The key elements you should look for when evaluating the database technology you're going to use should include:

• Transaction support
• Availability of native or 3rd party backup and replication
• Ability to run on your hardware (Windows, Mac OS X, Linux, Unix, etc)
• Scalability
• Licensing scheme (per user [CAL - Client Access License] or per processor)
• Availability of DBAs (DataBase Administrators) & Developers

Once you have decided on your backend database platform(s) - you can now turn your attention to the User Interface portion of the technology hunt. There are also loads of products on the market that all do the same thing: create user interfaces and reports for data that "lives" in a SQL-based database: PowerBuilder, Crystal Reports, Visual Basic, Servoy, etc. In next month's issue I'll get into the topic of User Interface Builders - the good, bad and the ugly - as well as whether you should do a strictly web-based user interface or "rich client" interface or both. For now, just know that you will need to build the user interface in SOMETHING - as SQL databases know nothing about user interface elements - only the data.

Step 4 - Looking At What You've Got

Once you have a basic idea of the backend database you're going to be using - it's time to take a look at what's going on in your existing solution. There are number of ways to accomplish this: if your current application has built-in documentation tools - now's the time to kill some trees and print out the "big report" of exactly what you've got going on in your solution. Unless you wrote 100% of the solution yourself - chances are extremely good that you will run into fields, layouts, scripts and other elements that:

• Have not been used in years
• Their original function is all but forgotten
• They are used... somehow... somewhere
• They were used for testing something... sometime and left in the solution
• They were created by a color blind alien - and written in their native tongue

Then there are the application specific "work arounds." Try to identify as many of these as you can (for FileMaker developers look for global repeating contain fields!) - and try to cross reference what other objects (scripts, layouts, buttons, etc) depend on them or refer to them. Yes, it can be a helluva' lot of work. Yes, you will probably have a couple (or a dozen) 4-inch binders of paper to sift through. BUT, you will gain some fantastic insights into how the solution works, and you will also save yourself dozens (if not hundreds) of hours of work when you get to later stages.

Ideally, you should look at each form (or layout) - and map out what it does, and how (and why if you can find out) it's used. This goes for both data entry and report forms. Generally speaking, you will only need to "migrate" the data that is displayed on input forms and reports. All the other fields (in the case of a FileMaker solution) like calculated keys, globals, etc. are for functionality and are probably used for scripts and relationships between files (i.e. sorted portals) that don't need to exist when using other Interface builder tools.

The other BIG thing you need to evaluate - ESPECIALLY if you're migrating a FileMaker solution - is the maximum LENGTH of data in each TEXT field. FileMaker allows you to put up to 64,000 characters in EACH text field on EACH record. While that's possible to do in most SQL databases as well - most of your fields in your new SQL table will be of type VARCHAR (variable character) and it MUST have a fixed length (maximum number of characters).

Yes, I know - it's tons of work - but you'll either do it up front, or you'll do it later - but you WILL do it.

Step 5 - Look At What You Want

If you "took your medicine" and did step 4 correctly - then you can also see where there can be process improvements, additional (or fewer) data fields, new reports, etc. I've seen some solutions where the original assumptions and business rules the solution was trying to enforce were no longer valid, and yet the solution was still "active" and people "...just left that field blank because it doesn't really apply anymore..." This part of the process should also include some needs analysis, and a polling of the (gasp!) actual people that are using the current version to discover what other new features are needed, which old ones aren't really "features" anymore, and what things they need or want that would help them be more productive.

To keep your sanity - you should also have a method to prioritize the new feature requests and enhancements. Otherwise not only will you never get the project finished - by the time you do, the requirements will have changed again.

Step 6 - Design BEFORE You Build

If you've done step 4 to the best of your abilities - then it will be fairly easy to design the "guts" of the new solution. Simply take out your screen captures of the reports and input forms and begin to design your new data model. If you're migrating from FileMaker, here's some additional things you should be aware of: • Each FileMaker "database" is the equivalent to a "table" in SQL
• A single SQL "database" can hold hundreds - if not thousands of tables
• Clean up your field names - do NOT use spaces in field names and you can't use high ASCII characters (bullets, tildes, copyright symbols, etc.) in field names either
• There are no such things a "global" fields in SQL
• MOST SQL databases don't allow "calculation" fields
• Data entry validation is done in your user interface, NOT on the field in the database
• You don't have index all the fields in a SQL database just to have fast searches - in fact too many indices can actually slow down the searching

Once you have a basic model (I like to use Microsoft Visio for this) of all the tables and all the data entry fields you'll need - then you can build a basic model and do a preliminary import of the existing data - so you will have a some "real" data to test with while you investigate your user interface builder tools.

Step 7 - Creating the new schema

You can create your new schema (the database, tables, and fields in each table) via raw SQL statements [i.e. CREATE TABLE CUSTOMERS (custId int, first•name varchar(25), last•name varchar(25)) ] or with graphical tools (Microsoft Visio, IBM's Rational Rose, or scores of other commercial, freeware and shareware tools) - or just simply use Servoy. Servoy will allow you to create tables and fields directly (you have to create a blank database first - and setup a connection in the "DB Servers" tab of the "Preferences").

Fixed Field Lengths

One thing that you need to keep in mind if you're migrating from FileMaker Pro - FileMaker allows up to 64K of text PER FIELD, PER RECORD. That's a boatload of data! ALL SQL-based databases require that you have a fixed-length field. That means that you have to tell it what the maximum number of characters is allowed in each text field. This can be difficult to figure out if all your data is in FileMaker - since people can basically type whatever they want into any text field. If you're using FileMaker Pro 5.0 or higher - here is a routine that you can put into each file that will help you determine the maximum length of each of your fields:

Add the following fields to each file:

Field Name	Type	Notes
gFieldList	Global	Names of all fields in db
gCurField	Global	Name of current field
CurLength	Calculation	Length(GetField(gCurField)) result is NUMBER
MaxLength	Summary	Maximum of CurLength

You can then set up a simple, single step script to set the contents of the gFieldList to the names of all fields in the current file:

Script Name: Get Field Names
Set Field [gFieldList, FieldNames (Status( CurrentFileName ), "" )

When you run the script "Get Field Names" it will put all the field names into the global "gFieldList." From there you can either use a looping script to walk through all the lines in the gFieldList, or just copy/paste each field you're interested in determining the length for into the field gCurField. Once the gCurField is populated you'll see the maximum number of characters for each field appear in the "MaxLength" field. Make a note of it (or store it in your own custom database) so that when you get ready to define your new SQL tables, you can be sure that you're going to have enough room to hold all of your existing data.

Unlike FileMaker - SQL databases have a couple of different types of TEXT fields. Here are some common ones:

Char (character) - Fixed length - padded with spaces to fill maximum length. Usually a maximum of 4,000 to 8,000 characters per field per record - depending on the database. Example: Assume a data length of 10 - and the data "Bob". The data that is stored in the database is actually "Bob^^^^^^" where "^" is a space.

Varchar (variable character) - Fixed length - no space padding. This is equivalent to "text" fields in FileMaker - but with a fixed maximum length. Usually a maximum of 4,000 to 8,000 characters per field per record - depending on the database. Example: Assume a data length of 10 - and the data "Bob". The data that is stored in the database is actually "Bob" (no trailing spaces like the char data type).

Text - Fixed length - no space padding. Like the varchar type - but usually the contents of a text field cannot be indexed. Usually a maximum of 4 GIGABYTES per field, per record.

No Repeating Fields

Repeating fields are a structure that is unique to FileMaker. There has been (and continues to be) a wide variety of opinions on whether repeating fields are "good" or "not good" to use in your FileMaker solutions. The good news is - if you have converted your older solutions to do away with repeating fields - you're all set for your migration to SQL because your data has been normalized. The bad news is - if you have a lot of repeating fields - you should probably convert them to non-repeating fields (stored in a separate database and linked to the "master" record by the record ID) before your migration to SQL. It will make your data migration MUCH easier.

Date and Time Fields

Another difference between FileMaker databases and SQL databases is the way they handle dates and times. SQL databases do NOT store date and time fields as different data types - but always as "dateTime." For example - if you have a date field in FileMaker - it contains only the date. You can store the equivalent in a SQL database but if the time is not specified, it will be assumed to be midnight of the date specified.

FileMaker Value: 8/21/2004
SQL Value: 8/21/2004 00:00:00

The time component is similar. If the time is specified, but no date, then the database will put in the first "aware date" of that particular database. For example:

FileMaker TIME value: 11:42:12 AM
MS SQL Server value: 1/1/1901 11:42:12
Oracle value: 01 JAN 1901 11:42:12

No Globals or Calculations

Another difference between FileMaker and SQL databases is that, with few exceptions, SQL databases do store calculation formulas that evaluate inside the database. In general, only the calculated value is stored, not the actual calculation itself. It's the user interface (be it VB, JSP or Servoy) that contains the calculation itself - and only the data is stored inside the database.

The "Global" data type in FileMaker is unique to FileMaker databases only. There is no data type for globals. Instead - you can use variables in your user interface to store temporary values - or you can create a special table if you're storing preferences or login, etc.

Putting It All Together

Once you've decided that migration is the right answer, and you've analyzed what you have as well as what you want, and you've looked at your current database to check data types and lengths, there is one more aspect of your data design to consider: how the data will be used. You should "clean out" any unused fields - or fields that you don't know what they do (I've seen people with databases that contain multiple "price" fields - one for each year) and optimize your data design as much as possible. The whole discussion of data normalization is far too big of a subject here - but for those of you just starting out on the road to SQL here's a very, very, very general rule: you shouldn't store the same data in your table more than one time. For example, if you currently have fields "phone1" and "phone2" and "phone3" in your customer database- you should consider making a new table called "phones" that is related to the customer database by customer•id. This would give you the flexibility of having an unlimited number of phone numbers per contact. Same with repeating fields.

As you can see - there are a lot of issues to consider when changing the data back-end of your existing solution. I hope you also see that you MUST take a good look at your existing solution from a data perspective and take the time to DESIGN your new schema correctly from the get- go. If you take the time before you start - you will have an extremely fast, scalable and flexible data structure on which to build your application.

Stay tuned in next month's issue for Part 2 of "Upsizing (Migrating) Your Solution" where we will take a look at the aspect of the graphical user interface.