The problem

In the current CMS schema even if all tables (for mysql) are declared ENGINE=InnoDB and
InnoDB table type does support Foreign Keys, we still have the same schema as we were stuck on MYISAM without FK support

The proposal

0. Review the CMS schema
1. Define Relations between Tables/Entity
2. For each Relation define the type of relationships (1:1), (1:n), (m:n)
3. Define the foreign key
4. For each FK define actions ON UPDATE / ON DELETE
5. Implement the DBAL to manage FK

Expected result

the referential integrity is managed by the DB layer using FK

Actual result

The pseudo Foreign Key are made by code
as for example ( #__menu , #__ menu_types) etc.

Example

A menu type may have (0..n) menu items
A menu item must have (1:1) and only menu type

ALTER TABLE #__menu  
ADD FOREIGN KEY ('menutype', 'client_id')
REFERENCES #__menu_types ('menutype', 'client_id')
ON DELETE CASCADE
ON UPDATE CASCADE

Additional comments

postgresql & mssql already supports Referential Integrity/ Foreign Keys