At 10:26 AM 16-12-02 -0800, you wrote:

>I have 3 tables that make up a class registration system. I have classes,
>sections, and enrollments. One class has many sections. One section has
>many enrollments. Actually, there's a 4th table: customers. An enrollment
>is linked to both a section and a customer.
>
>Classes table has a single primary key: classid. Sections has a 2-part
>primary key: classid and sectionid. Classid is also a foreign key, which
>references the classes table's primary key. Enrollments has a 3-part
>primary key: classid, sectionid, and enrollid. Classid and sectionid also
>make up a foreign key which references the sections table's primary
>key. Enrollments also has another foreign key: customerid, which
>references the customer table's primary key.

Herein lies part of your problem. IB/Fb databases are not Paradox, but
SQL. You neither need nor should implement hierarchical primary keys in an
attempt to enforce foreign key relationships - the DB engine *knows* what
to do about foreign keys.

Your keys for these three tables should be:

Classes: PK classid
Sections: PK sectionid, FK classid references Classes(classid)
Enrollments: PK enrollid, FK sectionid. Note that you don't reference
Classes from this level if an enrolment can only refer to the class pointed
to by the sectionid. If an enrolment can refer to more than one section
and/or more than one class, then you have a many:many
relationship. Although M:M relationships can be implemented in SQL using a
hierarchical structure, for performance and other reasons, you would
typically use enrollmentid as a surrogate key for each combination of
sectionid/classid, and apply foreign keys to each of sectionid and classid.

You don't explain the relationships that customer has with enrolment and
section, but in some fashion it is this redundancy that is causing the
problem you describe below. In SQL terminology, you have either a
transitive or a circular relationship involving customer - neither of which
is desirable in relational logic.

>Ok. In my app, I have a classes screen and a sections screen. On the
>classes screen, I have a TIBOTable for the Classes table. On the screen, I
>show the classes fields, and I have a TDBGrid which shows the sections for
>that class. I also have a button to add a new section for the current
>class. On the sections screen, I have a TIBOTable for the Sections table,
>and I also have a TDBGrid showing all the enrollments. (Both grids are
>attached to separate TIBOTable objects, set up with the proper
>master-detail properties.
>
>When the user is on the classes screen and clicks the "add new section"
>button, I switch to the sections screen, and do an Append() on the sections
>table. The table's OnNewRecord event fills in the section's classid field
>with the classid of the current class. It grabs the next available
>sectionid for that class, and fills that in as well. It then leaves the
>record open for the user to fill in the rest of the Section fields. There
>is a TDBNavigator on the screen, so when the user is done filling in the
>fields, they click the check mark to save the record.
>
>All this works beautifully almost all the time. But, I've recently had two
>different customers complain that once in a while on certain classes, when
>they click the "add new section" button, the new section comes up with
>customers already enrolled in it! This did not seem possible to me, given
>the key structure that I have set up, so I had one of them go through it
>with me on the phone. Here's what we observed:
>
>Go to the class and click on Add New Section.
>Section #16 is added, but it's enrollment grid is already populated with 5
>customers.
>Click on the checkmark to save the section. The record (section #16) is
>posted successfully.
>Go back to the Classes tab (same class), and click Add New Section again.
>Section #17 is added, and it's enrollment grid is already populated with
>the same 5 customers.
>Click on the checkmark to save the section. The record (section #17) is
>posted successfully.
>Repeat two more times for sections 18 and 19.
>Go back and check all 4 sections. They ALL have the SAME 5 students in
>them!?!?!?
>
>This does not seem possible, given my key structure. The enrollment grid's
>table's master-detail relationship is based on the section's classid and
>sectionid. If these are all the same enrollment records on each section,
>they should only be able to link to ONE section, yet each section has a
>different sectionid. But there they are.
>
>I had the user exit my app and go back it. We found that all 4 sections
>were still there, but the "phantom" enrollments were all gone from the
>grid, as they should be.
>
>The customer was happy with that, but I promised that I would get to the
>bottom of why it happened. I can't figure it out. As I said, it has
>happened to another customer of mine as well, so I need to get it fixed.
>
>Any ideas?

Yes, quite a few, but I don't think you want to hear them. The only thing
here that is pertinent to IBO is what you have applied as MasterFields and
KeyLinks for each dependent table. With your current structures, the
MasterFields and the Keylinks should be the same. If you are sure they are
correct, then you need to look at that redundant relationship which
customer has with sections and enrollments.

regards,
Helen