Question About Lack of Database-Level Foreign Key Constraints in OpenEMR

[Clare_ddl (Clare Wang)]

I’m new to OpenEMR and have noticed something intriguing about the database structure that I’m hoping to understand better. It appears that there are no database-level constraints enforced between foreign keys, and I’ve also observed inconsistencies in column data types across related tables.

Can anyone explain why the decision was made not to enforce these foreign key relationships at the database level in OpenEMR? From my understanding, implementing such constraints could potentially enhance data integrity and reduce errors.

Additionally, if there are specific reasons or benefits behind allowing different data types for columns that are supposed to be related, I would appreciate insights on this as well.

Thank you for your help!

[adunsulag (Stephen Nielson)]

If you search foreign keys on the forum you’ll see a couple of posts about this topic. This is a good discussion I did back in 2019 about it.

Database Schema Documentation

@stephenwaite So a foreign key is a database integrity construct that prevents your database system from getting out of wack. Here is a sample table from another application I develop. You can see the foreign keys on assignmentitem_id, last_updated_by, company_id, assignment_id, and created_by. [image] In this case I can’t delete my Company record (referenced by company_id) until all of my ReportFiles have properly been deleted. Otherwise I’d have orphaned records. Same with the other ta…

I started down that path but stopped as there were more pressing projects that needed to be implemented.

[adunsulag (Stephen Nielson)]

Another good reference point is a project I did recently that at least provide an updated ERD on the relationships of the DB for everything patient related.

https://two.openemr.io/d/openemr/Documentation/EHI_Export/docs/relationships.html

Explanation of the EHI export documentation is here.
https://www.open-emr.org/wiki/index.php/OpenEMR_7.0_B10

[mdsupport]

From DBA perspective OpenEMR database structure is stuck somewhere between “if ain’t broke” and “what about”. As a result many checks that can be done globally and consistently, are currently done in code (maybe). That implies adding a constraint at this stage would also need a robust mechanism to let practices fix some potentially problematic historical data and follow it up with review of the code. Not a trivial task!