I have a Film database that tends to grow very large because of large
text fields and some embedded images (I've already been over the
embedded vs linked images issue, so let's not go there).

The records I'm dealing with break down very naturally into
sub-categories -- lets say, by Movie Studio. In order to prevent the
database file from becoming too huge, I'm wondering whether I could hold
the records for each Studio in a separate FMP file -- each file having
the identical Table/Field structure -- and create some sort of central
"Command Center" file that would access each of the other files and
present/display the found data in this one place.

For instance, five separate files called

Disney
MGM
Paramount
Pathe
WB

Such that a search on a term in the SubjectKeyword such as "Paris" would
return the titles of films from all 5 files, in one place?

Is this a crazy idea?

Albert

In article <asteg-B8D7EE.12061226112006 (AT) news (DOT) west.earthlink.net>,
Albert <asteg (AT) mindspring (DOT) com> wrote:

This is no doubt possible, but I doubt you would save much if any file
space by doing this, and it would probably actually take more file space
because of the added overhead of each table and the relationship
structure. You are probably better off to stick with a single table.

--
For email, change <fake> to <earthlink>
Bill Collins

Yeah, it's a crazy idea... not a very good approach.

The way you save space in these situations is to use "join" files.

Suppose you are beginning with the structure:

Movies
=======
id, Title, Director, Studio, ReleaseDate

Now, to overly simplify things: if you've got 10,000 records, and "Studio"
contains the actual studio name (from your list) that's an average of 6
characters per record (assuming even distribution of studios) for a total of
60K of raw data storage plus overhead for indexing.

Suppose you added a table, called Studio, that looks like:

Studios
=======
id, studioName
1, Disney
2, MGM
3, Paramount
4, Pathe
5, WB

Note that this table (again oversimplifying) takes up a total of 35 bytes.
You never add records to this table unless adding a new studio. Then you add
a third table:

MoviesStudios
=============
movieID, studioID
1,3
2,1
3,5
4,3
5,2
6,4
7,1
8,3

And you see that this table occupies 30 bytes. It's just a "cross reference"
that says that movie #1 is associated with studio #3; movie #2 is associated
with studio #1, and so on.

Once you have these three tables, you can delete the "Studio" field from
Movies, and relate

- Movies::id = MoviesStudios::movieID (Allow creation of new records in
MoviesStudios via this relationship), and
- Studios::id = MoviesStudios::studioID.

This tells FileMaker exactly how they are cross-referenced.

In the old solution, you were storing the full text of each studio:

Paramount
Disney
WB
Paramount
MGM
Pathe
Disney
Paramount

The storage space required for this was 63 bytes plus indexing.

In the new arrangement, the total space used is 65 bytes (Studio table +
MoviesStudios table). However, that's just for 8 records. When you look at
thousands of records the space savings will be tremendous, because storing
two numbers per record in MoviesStudios takes up less space than storing the
whole Studio name in Movies. You can also do things like use "Warner Bros."
instead of "WB" and add just 10 bytes to the size of the file (and
automatically reflect the change in all relevant records) as opposed
thousands of bytes as would occur if you changed all instances of "WB" to
"Warner Bros."

Now again, this was a really over-simplified example, but if you apply this
technique to all the fields where it's appropriate (key words, genre,
actors, etc.) you will realize massive savings in your file size, because
numbers take up far less space to store and index than text does. As the
number of records grow, the same "information" takes less and less space.

Now, as far as segmenting on a *file* basis goes, the only reasonable
partition would be to take the images and put them into their own file. The
images are, undoubtedly, by far the lion's share of your file bulk. Putting
them into a separate file -- in conjunction with the join tables described
above -- will leave your primary data file very small, clean, and easily
backed up. It's also easy to relate the pictures file with the rest of the
solution, again using the join file method I described

"Albert" <asteg (AT) mindspring (DOT) com> wrote