Many or few registers in a database?

February 5, 200223 yr

Hallo

I’m a newbie to FM but have worked with other databases earlier. I have reed a lot of the discussions in this forum and think portals may be a solution to my problem but I need a little more information (help). I’m administrator to a database on a FM 5.5 Server with about 15 to 20 users on Mac and PC with FM Pro 5.0.

I will store a few thousands persons where every person may have taken up to a hundred different tests. Each test can be taken more than once by each person. Every test will have a first layout with information about the person and when the test has been taken. A second layout will contain the whole test.

Which is the best way to build this database?

I’m working with a database today that consists of about ten different registers there each register contains a number of tests. Each test is then displayed as a layout. The problem with this is that they didn’t think of that a person could do the same test more then once when they built it. Is it possible to solve this problem in the existing database?

Thank's in advance.

February 5, 200223 yr

By "register," do you mean ,"file"? It's fine to have the tests broken up into separate files, but it's most important to separate the persons file from the tests file (or files).

Each person should have a unique ID -- this is typically done with an auto-enter serial number. Then, each test the person takes is assigned the person's ID. Then you define a Relationship by that ID field, and you can display your tests in portals in the person file.

Also, if the tests are only distinguished by layouts, you should have a field in the tests file to select the name of the test, so you'll know which layout to go to! If you number your test layouts, then put the Status (CurrentLayoutName) into this field when a test is taken, it will make it very easy to later script Go To Layout (by field). You can then make it happen from a button in the portal row. Cool!

February 5, 200223 yr

Fitch has definitely put you on the right track, i.e. unique ID for each user. It is good practise to have a unique ID for every record in every database you create.

When you're dealing with a many-to-many relationship like the one you seem to be describing, e.g. "x can have many y, and y can have many x" then I believe the standard practise is to create a go-between file. In your case, you would have three files: People.fp5, Tests.fp5, and Results.fp5.

People.fp5 would store information for the people taking the test.

Tests.fp5 would store the information for the tests.

Results.fp5 would store the information each time a particular person took a particular test. You would only really need a minimum of fields in here: Person_ID, Test_ID and Grade. Of course, you'd probably also want to store the date and time the test was taken, etc.

You would then define a one-to-many relationship between People.fp5 and Results.fp5, and a one-to-many relationship between Tests.fp5 and Results.fp5.

Hope this gives you a good start. I recommend that you get your hands on a copy of Special Edition Using FileMaker Pro 5 by Rich Coulombre and Jonathan Price, ISBN 0-7897-2201-1. It is a must-have for every FileMaker developer!

February 5, 200223 yr

Author

Thanks for the quick response!

Yes, with register I mean file, it’s the name in the Swedish handbook.

The answers were pretty much as I thought but it's good to get it from someone who knows. As I understand the best way is to create a new file where I show information on each person and the tests for this person. In this file I will have one layout for each test, what I called “first layout” in my first post. Necessary relations will be created. Do you think I got it right?

I ordered that book yesterday after recommendation in a Swedish FM forum.

quote:

Also, if the tests are only distinguished by layouts, you should have a field in the tests file to select the name of the test, so you'll know which layout to go to! If you number your test layouts, then put the Status (CurrentLayoutName) into this field when a test is taken, it will make it very easy to later script Go To Layout (by field). You can then make it happen from a button in the portal row. Cool!

This sounds interesting. If you could give me a short example I would be very happy.

February 8, 200223 yr

OK, make a new script. Add a Go To Layout step (there's should be one there already by default), and look at the options for it, the little pop-up menu at the bottom.

One of the options is [layout number from field...]. It would be nice if "layout name from field" was a choice, but it isn't.

So if you make a field TestName, and you give each test a name that starts with a number, and you give each layout the same name as the test (starting with the number), then you make a script in the Tests file with the step Go To Layout [layout number from "TestName"]...

Now in the Student file, show all the student's tests in a portal. Put a button on the portal row, that does a script:

Go to Related Record [Tests, show only related]

PerformScript [External, Students:Go To Test by Name]

Get the idea?