Multiple files and relationships - when?

[David Holmberg]

I have a tiny question:

What limit should you experts put for how many fields that one file should contain without getting to big and slow?

In one of my files (1 of 7) I'm up to maybe 120 now (moving towards 250? before I'm done), and it's practical for me to have them in the same file, even though in most cases only 30 of them are filled in every record.

There are also many calculation fields and I thought it would be a good idea to have them in the same file as the fields that they are based on, am I right?

FileMaker Version: 6

Platform: Windows 2000

[Paolo]

Having a lot of fields shouldn't slow things down that much, neither make the file a lot bigger.

But if you have a lot of fields probably you haven't designed your solution in the best possible way.

There are many options for reducing the number of fields and having a better design and a more flexible, efficient, expansible solution; but they depen on what kind of data you are storing and what you are doing with it.

Tell us more.

[David Holmberg]

Well, I'll do my best.

I'm creating a training diary (to be used by some cross-country ski colleges and perhaps the swedish national team!). I'm a filemaker beginner but have worked hard to learn as much as possible during the last 6 months. Earlier we have used Excel to create our diary's but they grew to large (1 file with a size of 100 mb without any values. All the calculations made it extremly slow)

My filemaker files are:

COURSES (were you can describe the most common trainingcourses)

SESSIONS (where you can describe the most common trainingsessions)

CALENDER (with relations to TRAINING, DAILY and PLANNING). Day, week and month view)

PLANNING (including some timeline charts)

WELCOME (including login and some user options on how the diary will work (example week start day))

DAILY (with daily values like mood, sleep, weight etc.)

TRAINING - THE BIG ONE (with relation to COURSES, SESSIONS, CALENDER) (It will get about this many fields)

70 fields endurance training

- 30 standard (including two each for the 7 intensities (time and "session")(easier for the user to fill out than if I used fields with value lists)

- 10 race

- 10 "skitest"

- 10 "orienteering values"

- 10 endurance test

30 fields "explo" training

- 20 standard (with totally different info than in the endurance part)

- 10 explo test

50 statistic fields (calculation) (I want to have much statistic calculated automaticly)

50 container fields (with mostly buttons)

And maybe I'll need more fields in the future. Everybody has so many intresting ideas which I would like use.

Am I not clear enough? It's hard to go into to much details because it's rather big (or at least I think it is).

I am storing data of all kinds. For example:

Training - name, type, date, user, shape, comment, intensity time*7, intensity session*7, main intensity, sport, max puls, avararge puls etc.

Want to summaries it, show some of it in the calender etc.

I really feel there are no structure in this post. Maybe I should try to attach a file? But the layouts in the files are so far only caos.

Well, this is a start.

[Paolo]

First, container fields for buttons should be global.

You may start placing those in another file and accessing them via a relationship.

(But do you really have 50 different buttons??)

Second:

>70 fields endurance training

>- 30 standard (including two each for the 7 intensities (time and "session")(easier for the user to fill out than if I used fields with value lists)

>- 10 race

>- 10 "skitest"

>- 10 "orienteering values"

>- 10 endurance test

what do this fields actually store?

It seem to me that you may put this information in related files.

[David Holmberg]

The container fields are global fields with container data, I wrote wrong.

It would be a good idea to put them in another file, then I could access them from all fields using relationships.

They are not all buttons, it's maybe 30 buttons. Navigation buttons between the files and between the layouts in each file. Print, info, help buttons etc.

5 fields for storing parts of a scroll bar. A intensity list and a activity list with colorpics asigned to each intensity/activity.

some examples of the 30 standard fields:

- name, type, date, user, shape, comment, intensity time*7, intensity session*7, main intensity, sport, max puls, avararge puls, distance, velocity etc.

This is the main layout, the race-, skitest-, orienteering values- and endurance test layouts are linked to this one. They can all be part of a training session. First I had these values in different files but I thought the connection between them and the main site didn't get clear enough. I can do 3 training session in one day and one of these session was a orienteering race, then I want the race- and orienteering values to be tightly linked together with the right training session in the main site.

Just to give you an example of fields in the smaller parts:

Own time, winner time, result, flow, snowtype, air temp, snow temp, humidity, track quality, race distance, oxygen uptake etc.

[Paolo]

You may have a "RACE" table and connect it to "TRAINING" table with a 1 (TRAINING) - n (RACES) relationship.

When you have 2-3-4... fields for the same kind of information you should always considering storing that information on one field in 2-3-4... records on another table.

---

Also you may try this technique: you have a lot of fields on one record, but for every record you use only a small part of them.

I'll make the example of the product table of the computer shop...

On a "PRODUCT" table you have

the fields

CODE

DESCRIPTION

TYPE (C=computer, P=printer, M=monitor)

PRICE

always used, and the fields

CPUSPEED

RAM

HD

PAPER SIZE

RESOLUTION

PPM

RESOLUTION

TYPE (F=FLAT C=CRT)

SIZE

Some of those fields are never used (don't apply) on one record depending on the product TYPE.

You may reorganize the second group of fields this way:

SPEC1TYPE: "Cpu Speed" or "Paper Size" or "Screen Resolution"

SPEC1CONTENT

SPEC2TYPE: "Ram Size" or "Printer Resolution" or "Screen Type"

SPEC2CONTENT

SPEC3TYPE: "HD Size" or "Printer Speed" or "Screen Size"

SPEC3CONTENT

You got the idea? One field specifies what kind of data is storing the subsequent field.

Every SPECnTYPE can be a normal text field or a calculation based on the TYPE field.

I don't know if you may apply this tecnique to your solution. But there a lot of tricks like this, for many different situations. Maybe you have to invent your own...

Hope this helps

[David Holmberg]

Thanks!

This sounds good and I think I understand it. Will try it and see if it works out well.

Thanks again