multiple files vs master file

[rhakka]

Here is a design question for you all: how far do you go with separate, related files vs. larger files to simplify relationships?

for instance, I'm working on a design helper suite for radiant floor heating systems. This has a ton of parts in it, such as heat loss calculations, pump head calculations, customer information, building information, etc etc etc.

Many times I need information specific to the components of the system. Various components that carry their own information are things like Heat sources, tubing types, pump sizes, electronic controller types, heat exchangers, manifolds, fitting types, etc etc etc.

Needless to say, this is a big project.

Now I could have a separate db for each part type: tubing.fp5, heatexchangers.fp5, etc. The issue with this is making sure that new parts make it into the correct databases: there is a master "inventory" file with general information on all of them, then they have to be in the appropriate "part info" database for the design suite to reference it. I'm looking at about 15-16 files just for parts, plus the others, means I'm looking at a total of somewhere around 30-40 files for the design suite alone. Many of which are very simple, 2-5 field databases that the user will never open directly.

The other thing I could do is consolidate them into one larger database and rely on relational pulldowns and such to find the correct records and pull the correct information. This has the downside of having lots of fields for each record that will remain mostly blank, though it would simplify my script writing somewhat for adding parts.

Now the purist in me says, make as many files as necessary, that's the "best" way. But the pragmatist in me can see that perhaps a single file would be easier for much of the design.

Are their compelling reasons to go in either direction on this? What would you do? I'm confident that the relational capabilities of FMP will be adequate either way. Is this just a matter of personal preference?

[LiveOak]

It is a matter of personal preference, but also of how the structure relates to the operational part of the system. For instance, if each part type has its own file of information and you add 2 new part types a week, does this mean adding two new files and configuring them every week? Are the files of part unique information part of a complex system that uses part parameters in calculations or is the technical information on each part just advisory? Would your master record have 50 or 500 fields if you included the data for different part types? These parametes might pull you in different directions on the issue of one or multiple files. The ultimate decision is always a tradeoff or the idealized relational structure and the practical concerns in building your solution.

-bd

[Anatoli]

Relational = fast, small, manageable

Flat File = big, slow, data nightmare

[rhakka]

absolutely but we're not discussing relational vs flat file, both solutions are relational.

The part types (individual files) would remain static when I finish the vast majority of the time. The only additions would be particular parts within the part files. For instance, the tubing file contains pressure calculation amounts for different diameter tubing, where each diameter and type of tubing has its own record. The design database calls up the calculation information for the tubing being used in a design. Someday I might need to add a new diameter or tubing type, but it woud go into the existing tubing.fp5 file (or masterpartinfo.fp5 if I go that way). The same situation would exist for the many part files and other design information files that exist.

I'm actually leaning towards the master reference files: later when information must be added to the files, I am not entirely confident that the end user will make sure the correct files get updated. Having one file that simply uses keys to determine whether a record is for tubing, or heat exchangers, or what have you seems like I could script the data entry process with prompts to autofill the key fields more easily than scripting a guide to separate files.

For instance, a new part is added to the regular inventory file. I could then prompt the user to see if the part is one that the design database needs to "know" about, and prompt for the type of part it is, having the script guide the user and/or autofilling the key fields. If they are multiple separate files, I suppose I still could, but instead of scripts interacting between two files it would be between 15 files or more, which seems like it would be much harder to update later on if necessary from a developer standpoint.

bah, decisions, decisions. Would either of these solutions be particularly faster to process or can anyone think of any reason why one reference file with the correct keys should not be used, given the related recordfinding ability of FMP5 with its relational pulldowns and such?

[LiveOak]

I don't think the experience to the user will be that different in any case. The major issue would seem to be maintenance of the files when new part types are added. Using separate files has the advantage of speed, but the disadvantage of a greater complexity as part types are added. It is probably a game of numbers. If we are talking a maximum of 10 part types, either solution will probably work. As we get to 500 part types, keeping them in the master file leads to a slowdown and making each a relational file would require 4 copies of FM Server! If you have a reasonable number of known part types, I'd probably go with the separate file approach.

A key element is WHAT you are going to with the technical information on each part. If it will be part of a system to perform automated calculations for system sizing, flow rates etc. , it must be stored in unique fields. If it is just for lookup purposes, dump it all in several generic notes fields, treat multiple part types the same and save all the grief.

-bd