There are three design areas that seemed to come up more often than others when I have had to “fix” database files.
First - and maybe ProVue is a teensy bit responsible - is the user is thinking horizontally (fields) instead of vertically (records). I’m guessing that’s because their introduction to data was a spreadsheet; like a pro-forma statement. They’d see months or years as column labels across the top and various categories of profit and cost for those months down the page. And ProVue would say something like, “The database that thinks it’s a spreadsheet”. So people would use that structure to hold their data - each day, week, month, or year would become a new field extending horizontally across the data sheet.
Instead, the categories should have been the fields with one field holding the time interval (day, week, month, or year).
As data is entered, you should see your database growing in records, not in fields.
The second area was duplicated data. I once had to correct a business address and the user wondered why it took so long. In that database, the address was entered in at least three different places - and I had to go through more than 20 procedures in each of at least five different files to find them all. And they were entered in three different ways - hard coded into a procedure, live entered into a field, and displayed via a flash art object. How much easier to have that data in one place - even if in its own database with one record - and just link to it when needed. If it needed to be changed, it only had to be changed in one place.
Another place this shows up is form is Duplicated (cue Gary Larson starting another “Trouble Brew’n” series. One form has editable fields for data entry, the second form has fixed fields for display. Time passes - the user needs a change on the form Just something little - how hard can it be. Except it needs to be change not on one form, but two.
As a general rule, try not to duplicate.
The third area is not seeing the usefulness of an intermediate file. A standard example is the classical - I want to see all the classes this student has taken or I want to see all the students who have taken this class. Usually I’ve see this attempted by creating a student record and adding a record that copies over class information. Hopefully the user avoid the first issue and adds a record for the student with class info instead of adding a new class field.
The same thing is happening in the class database - a new record (instead of a new field) is added for each student. But this often falls into the second issue as class info is copied into the student record, causing some data duplication.
One solution is to create an intermediate database that just has the student ID, Class ID, and Date. If the student takes a new class the student record (profile) doesn’t change - a new record is just added to the intermediate file. Now if you want to know all the classes a student took, you can select all the records for that student in the intermediate file and display (lookup) all the class names (and other data) via the Class ID from those selected records. Works exactly the other way - all the students who took a class - select the intermediate records for that Class ID, and display the student by the student ID in those selected records.
The above is students and classes - but the same structure comes up again and again. For example, “What products has this customer ordered?” and “What customers have ordered this product?”
A “half-issue” might be considering the difference between history vs current time. The user wants the invoice database to pull over product information either when the invoice is displayed or a product ID field is exited. But if a product price changes and you are looking at an old invoice, you don’t want those fields to update to the new product prices. You want it to stay a historical record of what happened at that time. And that can get tricky because if you say, “Don’t change anything if some field already has something in it.”, then you’ve sort of locked it. If you don’t want to have to clear the field first, perhaps you can modify your “lock” to allow a change if, say, the Option key is down on exit.
But mainly try to avoid growing the database horizontally, entering the same data in multiple places (that goes for duplicating procedures instead of linking to one), and consider the advantage of having an intermediate “linking” database to bring associations together. Again - not “hard and fast”, no exceptions. More like considerations.
Now this is all “old style” technique/technology and PanX - especially PanX 10.2 and the new server - will probably have alternate/better/sutomatic solutions to some of those issue. I was probably influenced too much by the more mature Twilight Zone - Outer Limits. The shows tag line was, “… We control the horizontal. We control the Vertical …” sometimes automatic is good. Sometimes it’s good to be in control.
