@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.
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 table types. The foreign key will also make sure I can’t insert erroneous data. I can’t insert a ReportFile record for a company that doesn’t exist. Another nice benefit is that I can set my foreign key to cascade which is also a nice database feature so if my Company is deleted it automatically deletes all of my ReportFile records.
Foreign Keys aren’t necessary unless you have parallel applications or someone accessing the database directly for administrative purposes. If you do have those you want the foreign keys to make sure someone or another application doesn’t inadvertently screw with your data. I’ve seen on other forum posts that OpenEMR wants to support parallel applications working directly against the database. @brady.miller I’d recommend adding foreign keys to the OpenEMR codebase if we want to support this use case.
The downside to foreign keys is that a foreign key will slow down database inserts, deletes, and updates. This isn’t usually significant unless you need to batch process thousands, or hundreds of thousands of records in a short period of time. There are database patterns to get around that though (denormalizing the data etc).
The only concern this late in the game is that there is possibly bad data in the database. Adding the foreign key constraint will reveal that data immediately. It will also start rejecting code in the codebase where the data integrity is being violated which is a good thing. Lastly it will force column consistency that Dan Ehlrich (sidenote 2 user tag limit for new accounts??) talks about as the foreign key column and column definition must match the column type exactly.
The other benefit of the foreign key is that ORM systems like Doctrine will autogenerate code that lets you fetch and work with all of your related entity types when you have foreign keys setup properly. It also makes the generation of ERD documentation much easier as the documentation engine can discover immediately the relationships between tables.
So there’s a bunch of pros and some cons of adding Foreign keys to the OpenEMR codebase and it’d take some time. I’m interested in taking on the project if the community wants to go that route. The downside is there will be a lot of data cleanup if there are violations of the foreign keys for anyone who wants to upgrade their system to the latest OpenEMR version.
Lastly, I’ll just say I’m speaking from my experience. I’m not a DBA but I’ve worked with database systems for a long time.