by Jeff Moses
| Jeff thought Alpha Five was losing his records, but then he discovered that he had done it to himself through an imperfect understanding of Referential Integrity. Jeff is not only big enough to admit his mistake, but he's willing to broadcast it so others can learn from it. You can commiserate with Jeff at active@en.com |
From time to time users have commented that records are mysteriously disappearing. We too have wrestled with this problem for many moons. In our case we have located the culprit and I am sharing our findings here. This may not be everyone’s answer but I’m sure a few of you will find the information useful.
In Figure 1 I have illustrated the original set structure we were using in our Invoice and Inventory system for the Repair Order. The tables that are colored red are the only ones of concern to this article.
Figure 1. Original set structure.
If you look at Figure 1 you will see that the parent table to the set is rprorder_header which is the Repair Order system header. Connected to that using a one to many link with referential integrity is the ro_items table which connects any items purchased in the transaction to the rprorder_header. The next link is product which was originally connected as a one to one link using referential integrity (cascade changes, deletes). The linking field between them is Supplier_ID.
If you look at Figure 2 you will see our RO form and if you look at the middle browse that has the items in it you will see a column header called QIS. This field is the Quantity In Stock field displayed directly out of the Products.dbf. Its only purpose for being included in the set is so it can be displayed in the browse to show real time stock quantities for the product. If a product is out of stock it can be back ordered at that time right on the form by entering a backorder code in the "ENTRY" column.
Figure 2. Repair Order form. Jeff wants you to know that the artistic talent behind this form comes from his partner, Tom Hlavin.
Now that we have a brief understanding of how the set works let’s look at Figure 1 again. Taking a close look at the original link between ro_items and product you can see where I have the problem. In actuality just looking at these two tables, ro_items is acting as a parent table to product. By not paying attention to some very basic functions I originally used referential integrity in the link and therein lies the problem! The first time an item is entered into the browse everything is OK. The problem is if you go back to an item that has already been saved and change or delete it. By having the tables connected with referential integrity all changes or deletes in the parent were reflected in the child. When an entry's Supplier_ID was changed in the Repair Order form by editing one of the entries, it also changed or deleted the corresponding record in the Product.dbf. For a change, I would end up with a duplicate Supplier_ID. In the case of a delete the record was actually deleted.
If you look at figure 3 you will see the corrected set structure.
Figure 3. Corrected set structure.
After this correction we have not lost any records or had mysterious duplicates showing up. I guess the lesson here is "more is not always better." I was so impressed with the referential integrity option that I lost sight of what it really did and used it everywhere without thinking about the results. I hope this will be of help to others who may have this problem while they still have some hair left!
1/23/00
Don't forget, we need your feedback to make this site better!