Why would this result in 500,000 fields? Why do you even have 500 fields just to document appointments?
Best guess is that you are adding more and more fields when you should be adding multiple related records.
I could try to use related records to minimise the fields, but this would make the GUI much more difficult to create :
i.e. for each client, and in each appointment, there may be : 1. A number of rating scales filled in (out of a possible of 10, with each scale having about 40 questions), 2. A number of tests sent (out of a possible 300 ). For ease of use, the tests are sorted by category in a tabbed layout, and the user ticks the ones he wants.
If each test is selected from another related table (child to the appointment), then I 'd have to use a portal for adding the tests and not use ticks.
You need to use the portals and you can still use the "ticks" when you do. Portals, in fact are one way to set up what looks like a field of check box or radio button values, but with a scroll bar to scroll through the values--just to cite one possible example.
What you describe here by adding more and more fields is very unlikely to be workable in the long run.
Yes the GUI will be more complex to design, but should not be more complex for the user to use. And the payoff will be a much more flexible and manageable data model.
Thanks for the answer Phil
to get to the crucial point :
let's say that I have a layout named "clients", which is based on table "clients". Each client may have various appointments. I have created a second table (linked to clients) named "appointments"with appointment dates, etc. The latter is displayed on the main layout on a portal. I 've set up properly table occurrences so that when I click on the "appointment dates" portal I see the related records from appointments on my layout. Everything works ok up to now.
Now let's say that for each appointment of a given client, I have set a tab on my layout, named "tests". By clicking on the tab, the user should see a list of 3 objects, each one having an option to be ticked on the right. The way I 've done it up to now, was that I created 3 fields for these 3 objects under "appointments" with 0 or 1 as values, which the user could see on the tab and click on them (clicking set the value to 1 which the user couldnt see, but this was important for its label to change color under conditional formatting).
If i use a related table for "appointments" named "tests", how should I set it up so that I can achieve the same gui result? I presume that the "test" table should have, besides and ID and a FK, a single field that should store the test ordered right? Does your previous answer imply that the table "tests" should have all 3 possible fields (let's say test1, test2, test3) that I could turn into checkboxes, and would appear in the portal as a row? If it is so, wouldn't this be the same (in terms of total field number and performance) as having the fields in the "appointments" table?
sorry for the lengthy post
The main difference here is not a much speed but flexibility--both for data reporting and for adjusting your set up as the tests to select also change. But trying to work with even 500 fields in a single table can be a real nightmare for the developer.
You would indeed have one record for each test the user might select. As to the GUI, I don't quite picture how you are doing this for multiple appointments with separate fields on a Clients based layout to begin with.
I'd put a portal to tests on the layout and use a script to "synch" the portal to the current appointment selected in your portal of Appointments. Since this portal can have a scroll bar and only shows records for a single appointment, that shouldn't be too difficult a layout to design. An alternative approach is to use a "horizontal portal" where you place series of nearly identical one row portals in a row with the first portal set up with Initial row: 1, the next for initial row: 2 and so forth. That's a bit of a bother to create, but can offer layout options different from the standard multi-row portal with a scroll bar layout design.
This is sometimes called a "master-detail" pair of portals and you can find a discussion of them here: Need layout solution for nested portals...