How many tables?

[halbj]

We have an elaborate collection of FileMaker 6 databases that relate to form a system that manages our business processes. We are looking at migrating to 7.

Is there a 'general rule' we can follow as to how (and how many) we should arrange our tables in files? Having 50 tables in one file does not seem logical to me. Does anyone know of any documentation regarding design principles in this regard?

I am reading 'Migration Foundations and Methodoligies' document and finding it usefull, but haven't been able to find reference to this.

Thanks

[Vaughan]

Not the answer you are looking for: it's too early to tell exactly what the best practice is.

The pros to all tables in one file are portability and ease of managing security and user accounts.

The cons are mainly related to backup a single file, especially if one or more tables get really big.

Multiple files may also make modularisation easier, especially for commerial products and development tools.

[Fenton]

I don't think it's possible to say. There are different possible configurations of the same solution. Who's to say exactly which one is "best," if they all work just about the same?

Generally I'd say, start with the easy and obvious. Line items files obviously could be a table in their parent file. It simplifies scripting, which is mostly passing data and controlling navigation between them.

Join files could be incorporated into one or other of the parent files. It may not matter which.

Look at any other small "utility" type files. They could probably go into a larger associated file. That may be enough right there.

It doesn't much sense to take a 50 file solution and gradually consolidate it to one. If that's what you want to end up with (but I don't think you do), you should definitely start from scratch. At a certain point consolidation of converted files reaches a point of diminishing returns.

It is a good time to really go over what you have, and try to diagram it (as best you can). Not so much to get a perfect ERD drawing, but to see generally how things are tied together, and what the most obvious consolidations would be.

[halbj]

Thanks for the replies...

I had planned to look at the system and identify logical 'Entities' and build files around them, as suggested by Fenton, keeping line items etc with parents. I think I will be able to manage that. I just wanted to check that I wasn't missing something...

Thanks again for the suggestions, I will consider them when planning.

[Kurt Knippel]

Rememeber that the seperation of files was a necessity of the 1 table per file limitation of previous Filemaker version, NOT a design or best practices choice.

First ask yourself why NOT have them all in one file? What is the compelling reason to not have all your tables in one file.

I have been doing this...as well as asking other developers...I have yet to find a compelling reason not to put everything into one file.

[slytle]

I have heard that it may be a good idea to have two DB's. One would contain the data only and the other would contain the interface. This way you could make changes to the interface without affecting the data and an upgrade could be done in minutes with minimal downtime of your DB. Any thoughts on this approach?

.sly

[slytle]

Here's a discussion of the method I mentioned above:

http://www.fmforums.com/threads/postlist.php?Cat=0&Board=separation&page=0

I didn't see that this thread had been spotlighted before I made my post...

.sly

[Fenton]

I would say that one good reason, from my developer's point of view, for not putting all your tables into one file is simply that it gets too cluttered. The 2 weak points are: the scripts menu, and the relationships graph.

I have had files that had a lot of scripts. It was often a problem to quickly find a particular script. I eventually came up with a "table of contents" system, so I could at least jump to the correct section. But it is so much easier to work in files with less scripts.

Of course the opposite is also true. If 2 files are having to bounce data back and forth, then it's a pain to switch back and forth to work on the scripts. Such files should probably be tables in the same file.

I would like to see at least one level of folders in the ScriptMaker list. That would make a huge difference to me.

Perhaps he has a lot files, but very little scripting, and fairly simple relationships. In that case a 1 file solution becomes more attractive. I don't know what he's got, so it's hard to say; other than the obvious, that 50 is a fair amount of files.

I often see Accounts and Privileges mentioned as a reason for only 1 file. Privileges, if they are at all complex, are set on a table by table basis anyway. It's a little more trouble to do them in a few separate files than all in one, but not that different. Creation and handling of Accounts should be scripted, from a central table. You have to import a few scripts into each file to handle this, but this is not that big a deal.

The original poster was talking about migrating a 50 file in-house solution to 7. If you're advanced perhaps separation of data and interface is easy for you (it's not for me); in which case go for it. But for him in his situation I wouldn't recommend that as his first foray into 7.

I would work on the 7 system until it is complete and tested - as if it was being used, entirely separate from the other system, then make the big change all at once (with no time deadline). This means on your own computer, with protocol set to none, then later with another machine for network testing; separate from the current solution.

[spankalee]

When I moved to 7 I divided up the files into logical groups: Invoicing, Customers, Products, Purchasing, Inventory, etc. Each file ended up containing from 4 to 10 tables.

My idea behind this was to seperate the DB in a way that the files would be interchangable with backups. If the Customers file got corrupt then I could replace it with a backup and any new invoices would still be intact. Also, I liked thinking of certain tables (like line items and thier parents) as strongly linked, and other tables (like Invoices and Customers) as weakly linked, and still others (like Products and Customers) as very weakly linked or not linked at all. I put all the strongly linked tables into one file.

I'm not really sure those are good enough reasons though. It still seems like a good idea, but it makes some things more difficult. Sometimes I have to duplicate relationship setups in two different files.

I assume FileMaker did a good job with the file format design and that it doesn't get internally fragmented or take a lot of cycles to un-fragment, so hopfully there's no performance hit to haveing one large file.

I'm not sure what I would do if I had to start over. The seperation model is compelling because right now I'm away from the office and it'd be nice to update scripts and layouts and let the office replace the interface file and keep the data intact. I may eventually create an interface file and still use the seperate data files.

[VFXdbGuy]

I am seeing the same thing as Fenton. Very large list of layouts and scripts in a single file solution. Navigation in both the layout selection menu and the script menu leaves much to be desired.

This is a problem for me as the developer but not for the user since most of the layouts and scripts are hidden.

After making everything work in one file I split it into two but not for the reasons I've mentioned or the ones in this thread. i have a very unique situation here. What I create is used as a template. New copies are made for each "project" we do resulting in large numbers of completely unrelated files, each with its own unique filename. I am putting together a synopsis of this bizarre arrangement in the hopes that there are others in the same boat

[321]

Has anyone tried using one file for all the data, then several files for interface? I started from scratch on a new system for my company and have been separating data from the interface. In progressing, I am finding there are not that many scripts in the data file, and the relationships in the data file are not that bad either (mostly for lookups, and sometimes for passing data for calcs). As for my interface file, it is "relationship graph gone wild!". Same as for the scripts. Although, you can label and catagorize scripts which helps a bit. Fenton's idea of a subfolder for scripts would be awsome. Anyway, I was thinking of using one data file (about 60 tables) and about 6 or 7 interface files. This would keep the graphs and scripts under control while keeping the data all together.

For the manys files, many passwords problem, I was thinking of using external authentication, so all passwords will change and update externally. Haven't implemented that yet, but I probably will. That way, I don't have to go through password hell when trying to change or add passwords/accounts in each file, as was the case in previous versions of Filemaker.

[rcassidy]

Well, I came here looking for some advice on my solution.

I've taken a slightly more than 100 table FM 6 solution and am reworking it in 7. I opted to separate data and interface and have multiple data files and multiple interface files. Data is separated primarily by user authorization - I download data from multiple sources, each of which has specific access requirements, so a file for each source (up to 60 tables per file - the table limitations have allowed me to set up the data more naturally than before) and I generate data which go into a file for each data category. Right now there are 5 data files, and there will be 8 when done.

I have different types of users accessing the data - some are primarily clients of the data - they view, search - others are maintainers of the data - they do entry, generate reports. There is an interface file for each type of user. In the end, there will be about 10 interface files - some for fairly specific activities, others very broad.

So far, it seems to work quite well mixing/matching data and interface files, and has helped to keep the interface more focused on specific tasks rather than focused on a specific data set. I too will use external authentication to manage users and passwords. (Releasing FM Server Advanced now would be very handy...)

I'm getting somewhat concerned about my primary interface file. The interface file just crept over 150 relationships and at the current pace will close in on 300 when done. Technically it can be done, but I'm not sure if it's wise. I can divide the file logically, but I lose the benefit of a certain amount of code and relationship reuse - I'll wind up with roughly 2 200 relationship files, and a ton of script duplication, which might be manageable with some extremely careful development to minimize cleanup of imported scripts from one file to the other. Of course, everything in one place would be ideal - if a bit difficult to manage.

So, my question is, has anyone really pushed the relationship count that far?

I'll point out that very deep relationship chains do bog FM down. I have a few occasions for 15 table deep chains on display, and they're pretty sluggish. Once things are working, I may have to go back and rethink some of these simply for performance reasons.

[Computer Geek]

I have recently gone through a conversion of about 30 files in FMP 6 to a 2 file data separation model in FMP 7. What I can tell you is that the journey was long, and I did all my homework first, and at times during the conversion, I questioned the wisdom of it. However, I am reaping the benefits of it now.

FMP 7 is worlds apart from 6, and I love the benefits that having all the data under one roof provides. I have an interface file for my scripts and layouts. The security and query times are both in the lead for benefits.

[321]

Comp G.

I am also using the separation model, but have not put the database online yet. One thing I am curious about, is where do you keep your interface file? (On the server, or one at each work station) I have a little 2 computer network that I've tested, keeping the interface file on the client and the speed seemed much better than accessing both through the network. I think the speed diference has to do with the graphics and layouts being stored locally. This was strictly using FM 7 and opening one file as a host, not using FM7 Server. That could make a difference for speed as well.