Recommended SQL Database and Table Structures
Recommended Database Table Structure
Universally applicable, such as for general use.

Note!
1.The database storage engine is changed from the default MyISAM to InnoDB for a perfomance reason. See the differences below:
○MyISAM is generally faster for read-heavy operations because it uses table-level locking, making it efficient for simple queries and bulk data retrieval.
○InnoDB is faster for write-heavy workloads and mixed read/write operations because it supports row-level locking and transactions.
○If you're dealing with large datasets, concurrent transactions, or need data integrity, InnoDB is the better choice. But if you're working with read-heavy applications where transactions aren't a concern, MyISAM might be faster.
The storage engine can be permanently changed. See the help topics below:
or
2.The reasoning for selecting the utf8_unicode_ci collation and can be permanently changed, see the following help topic:
○utf8_general_ci: Faster in performance but uses simplified sorting rules. It may not handle special characters or linguistic differences as accurately.
○utf8_unicode_ci: Follows Unicode standards for sorting and comparison, making it better for multi-language applications. However, it may be slightly slower due to its more complex sorting rules.
○If precision in sorting and comparisons across different languages is important, utf8_unicode_ci is generally the better choice. If performance is a priority and you don’t require strict Unicode sorting, utf8_general_ci may be sufficient.
The storage engine can be permanently changed. See the help topics below:
Case, Character Set and Collation
or
Final Requirement of Table Column Structures
The following SQL database and table structures are recommended for presenting the processed data to our technical director prior to submitting to PurpleXtra's live production database.
Diagnostics Table

Note!
1.The diagnostic (ICD10) codes are required to be a maximum of 4 characters and this applies to every country.
2.SQL table names should follow the specified convention. If data spans multiple years, a separate set should be created for each year.
○diagnosis_2024
Procedure Table

Note!
1.There is no requirement for the procedure codes to be a certain length.
2.SQL Table name convention could look like:
○procedures_2024
Topic last reviewed: 15/05/25