The database schema for osCommerce Online Merchant v3.0 has been updated to include foreign key relationships between related tables. This update allows MySQL to natively support foreign keys on InnoDB databases that are directly defined in the database schema, and uses a fallback mechanism for MyISAM databases where foreign key relationships are defined in a database table.
Foreign key relationships allows records to be linked together through multiple database tables, for example, linking products to categories. Until now, when a product was to be deleted using the Administration Tool, extra PHP code had to be written within the delete function to also delete entries in other database tables that shared a relationship with the product being deleted.
This is fine for standard installations where the relationships between database tables are known, however when add-ons create new database tables during installation, changes were required in core source code files to also perform database actions on the tables they created.
Now with foreign key relationships, changes to core source code files are no longer required and changes to the related database tables are performed natively on InnoDB databases or automatically through the database class for MyISAM databases. This cleans the codebase considerably and allows one simple query to take care of the whole database. For example:
delete from osc_products where products_id = 1
This one simple query now automatically takes care of deleting the product language definitions, product reviews, category assignments, special prices, shopping cart entries, and any other product table relationships without any additional queries or PHP code necessary.
The following foreign key constraints are supported for MyISAM databases for both ON UPDATE and ON DELETE operations:
- CASCADE, automatically update or delete child records when a parent record is being updated or deleted (eg, delete all product related information when a product is being deleted)
- SET NULL, automatically sets the child record field value to null when a parent record is being updated or deleted (eg, clear the product manufacturer value when a manufacturer is being deleted)
- RESTRICT, prevents a parent record from being updated or deleted if child records depend on it (eg, don’t allow order status levels to be deleted if they are in use by orders)
Add-On developers can take advantage of foreign keys by defining relationships directly in the database schema for InnoDB databases, and by entering relationships in the osc_fk_relationships table for MyISAM databases. Examples of foreign keys defined in the osc_fk_relationships table are:
The osc_fk_relationships table is only used for MyISAM databases and is used by the database class to check on the defined constraints when UPDATE and DELETE queries are being performed.
There are currently 68 foreign key relationships defined that will be introduced in the osCommerce Online Merchant v3.0 Beta 1 release.
The changes are currently available in my development branch at GitHub and will be pushed to the main development branch after further testing and code clean up has been performed. My branch is available at:
http://github.com/haraldpdl/oscommerce/
Documentation on MySQL’s foreign key implementation can be found here:
http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html