Database design considerations

[Ray H]

I am new to FM and am embarking on writing a FM application, it would be really helpful if I could get some general design tips from more experienced FM developers.

I want to design a system which would have approx 21 tables. Am I better spreading the load accross several databases or would FM be happy with them residing in one DB. Also, if I was to create several databases would I be better having all the layouts/scripts in the first initial database or is it wise to spread them out also.

And if I was to spread them out how can I prevent people opening up the databases with out first launching the opening master database.

Any tips would greatfully be recieved, I do not wish anyone to write the DB for me just to provide some theory into best practices.

Any advise you can offer a budding developer is much appreciated.

[Lee Smith]

Hi Ray, and welcome to the Forum.

I just answered a similar question, that you can benefit from to. Click Here

HTH

[stanley]

Ray:

The topic you are encountering is the "Separation Model." There have been tons of discussions on the relative values and uses of the separation model on these forums, and if you search for it, you'll probably find more information than you can absorb. Which is a good thing.

Once you have a firm grasp of the concepts, you can begin to delve into the specifics of your solution.

-Stanley

[Fenton]

Whether or not to use the 1-file or multiple files approach is one of those questions which has no simple answer; though you'll probably know more after you're all done -]. It depends on many things. The obvious start is to put any file which is obviously only a child of another into its parent, as a table.

The next step might be to put a file that interacts heavily with another file into the same file, as tables. It is just easier to deal with, especially in scripts.

Of course, with 7 you can also just put a Table Occurrence of the 2nd file in the 1st file's Relationship Graph; then it is much the same as if they were tables in the same file. You can even put layouts from the 2nd file in the 1st file; so you don't have to jump between files during scripts. The only real difference is that the field definitions of the 2nd file are always just in the 2nd file. But scripts accessing it can be in either file.

As you can see from above, many configurations are possible. If you decide not to go with the 1-file approach, then you will want to go with the "cluster" or "module" appoach, where each file is a group of tables which logically belong together.

I think that the most important thing is to be consistent in the logic you use for clustering. It is important that you know where to look for any particular thing. So the amount of consolidation should be as uniform as possible.

21 is too many files. I would think more like 3; but that's just a wild guess, as I don't know what you've got or the complexity. And, if each is simple, just put it all in 1 file and be done with it. Unless you want to go with the Separation method, and separate interface from data; an entirely different approach. FileMaker 7 has given us many choices.

FileMaker Pro Advanced (8) includes the ability to copy/paste entire tables, with all fields and their calculations. This helps greatly if you decide or need to move a table to another file (or "duplicate" an existing table).