Need help with database design

[Ivan X]

Hello,

I recently adapted a client's FileMaker 6 flat file database with 1700+ (and growing) fields in it to a relational design. In the old design, each record was a contact, and each contact had any number of projects associated with them. They spent most of their time on a layout where the basic contact info was at the top, and they would have their most relevant projects going down the side with project status going across. They'd change the projects they were looking at in layout mode, and simply reassign the fields as needed. When they needed new projects (which was often), they'd shut down the server and add them.

I thought having 1700 fields and adding new ones all the time was a bad idea, so I changed the design so there was a projects table, a contacts table, and a tracking table which joined the two. Sounds simple, but I had to jump through many hoops in order to preserve their existing style of working -- like, for example, I defined 20 self-join relationships within the tracking file in order to be able to have twenty projects on their project layout which they could see regardless of which actual tracking record they were on, and a "master tracking record" for every contact so that I could have a hide duplicates" button so they could scroll through that layout without seeing redundancies. I could, of course, have put the layout in the contacts table, but that would have led to even worse headaches, since most of their Finding needs to take place in the Tracking table.

The end result is that it's arguably better (though it is much more complex) under the hood, but from their point of view, usability and performance has gotten worse. They can't do finds the way they were accustomed to doing them, some finds they can't do at all (OR's are easy, but AND's are impossible, whereas they were trivial in the old system, as in all contacts who've received this project AND that project). And many finds take much longer because they are through relationships, like they want to see all contacts who received a certain project who have a certain status. That status lives in the contacts table, so it takes forever to find for when they're in the tracking table. So I used a lookup to store it locally in the tracking table, but then they can't change status from the tracking layout, where they live. Like I said, a lot of hoops, and each one carries another cost with it.

I am not really sure how to improve things at this point, and am not sure that I made the correct architectural decisions (though it seemed simple enough at first). Does anyone have any insight as to what I might have done/do differently? Would FileMaker 7 help with the speed issues of finding through relationships, at least? Any insight or thoughts would be very much aprpeciated.

Thank you,

Ivan.

[Fenton]

A couple of things. First, FileMaker 7 is much faster at related Finds. They were almost unusable in large files in 6; now they are usable, but still much slower than indexed files.

Another thing to consider. If you can control the Find, by scripting, and they are willing to put up with a few limitations for often-used Finds, then you have the option to take the script to where the fields are local, and build a found set moving between tables. It is more complex, with much more scripting; but strangely enough it can still be much faster than a similar simple related Find.

The "key" to these operations is often the "Copy All Records then paste into a global" method, to capture the IDs of the found set, then use them relationally to isolate their relevant matches in another table. They can also be used, in their global multi-line form, with the FilterValues() function to limit an existing found set in another table.

There may be some upper character number limit to the FilterValues() function. But the old bottleneck on the text field size limit is sky high now, 2 GB (up from 64,000 in 6). So Copy All Records/paste is pretty safe.

On a couple of files I've put in quick "go to these in another table" scripts using Copy All Records. With say 5000 invoices it can go to all the relevant customer records in a second.

There is also Constrain Find. You can do a Find on an Indexed field, then use Constrain Find on an unindexed field. They seem to have fixed it, so that it only Finds within the current found set; much faster.

I just remembered another clever trick (not mine :-). Which is to use the Import Matching, matching the ID fields, but only importing a dummy global (you have to import something). After first showing all, this creates a found set matching records in another table. I don't know that it's any faster than Copy All Records though. Probably depends on the size of the matched table.

But, as I said, these complex operations would only be for Finds with known factors. But you can analyse the globals that they've entered, perhaps offer a few other toggles and global choices, so you can probably handle many often-used Finds.

Also, you might want to look for, or create, a new feature that they CAN'T do with their old flat structure ;-]