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.
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.
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!