Foreign Keys

What Is The master_2019.2

The master_2019.2 version is the most current version of SI, providing comprehensive foreign key support at the database level. The version prior to this, master_2019, provides only limited foreign key support via code.

This version is running in the developer’s production environment using PHP 7.2x and development environment using PHP 7.3x. It has not been tested with PHP 7.4x as yet. Because this version implements comprehensive foreign key support, it has not been flagged formally for production use until a number of users successfully upgrade to it.

This version also removes the Zend library from the library folder, maintaining it via Composer in the vendor folder. While the version in the library folder had been updated to remove deprecated class constructors and the Composer version doesn’t, it was felt that having an offical though deprecated version in SI was the better way to proceed. This change currently brings the Zend up to v1.12.20.

 

What Are Foreign Keys?

Foreign Keys (FKs) define a parent/child relationship between database tables.

For instance, an invoice is associated with one customer. But a customer can be associated with many invoices. This means a parent record in the customer table can have one or more child records in the invoices table.

By defining the FK relationship in the database, protection is put in place to assure that spurious adds, updates and deletions do not create orphaned records.

For example, the Recurrence feature uses the cron and cron_log tables. The cron table is both a parent and a child to the cron_log and invoices tables respectively. When a new Recurrence record is added and a new cron table record is added, that record must be linked to a parent record in the invoices table via the cron invoice_id field.

Later when a crontab action (automated repeating task) is processed automatically creating a new invoice, a record is added to the cron_log table to record the event. This child record must contain a FK (cron_id) that points to its parent record in the cron table.

Further, if  in phpMyAdmin, you attempt to delete a record from the cron table, without first deleting its child records in the cron_log table, an error is reported stopping your delete request.

By implementing the FK logic at the database level, utilities and programs that modify database information will not be allowed to do so without assuring that the FK rules have been met.

Should I Upgrade To master_2019.2?

As with any major update, unanticipated problems can occur. However, they will never be found and resolved unless people start using this update. That said, this version of the software has been running in a production environment for a number of months and several people have installed this software to help flesh out update issues not resolved.

The SimpleInvoices Group Forum accessed as the first menu item on the left, is the way to get help in resolving issues that you encounter. By signing up as a user on this group, you can submit issues, questions and comments to those developing SI and receive responses and solutions with reasonable turnaround; typically within 24 hours.

The database patches made by this version are extensive and rely on the database structure and data to conform to expectations established by the patch level of the version of SI being upgraded from. Typically, this works without issue. Sometimes however, unanticipated issues are encountered that cause the process to fail. These issues will be discovered as more users update to this new version. Support to resolve these issues will be provided as rapidly as possible when reported though the forum.

As with any update, make sure you back up your database and your SI folder (all programs and files) before loading the update. This way, push come to shove, you can restore to the previous version if an update solution is not provided in the necessary timeframe.