28
edits
Changes
no edit summary
The DEVICE table is the base table for capturing characteristics of medical devices; it has the following 10 columns:
The DEVICE table is the base table in the [[MDS Database]] for capturing characteristics of medical devices; it also houses the tombstone data for the MDS Device section in the user interface has the following 10 columns:
{| class="wikitable"
{| class="wikitable"
|+Fields on the MDSDB.DEVICE table
|+Fields on the MDSDB.DEVICE table
Of these, the DEVICE_ID is the most important, being a consistent and unique identifier used across the system to refer to a device. We also often use the TRADE_NAME, as it is more understandable to humans than DEVICE_IDs are. USAGE_CODE, PREF_NAME_CODE (PNC), and GMDN_CODE are various ways to group devices together, in increasingly precise ways, and RISK_CLASSIFICATION is used as a loose “risk level” of devices. The remaining fields are a pair of dates that we seldom use, a flag for whether the device is under the “new” regulations, and an “obsolete” flag, none of which we tend to use in our queries (but we could).
Of these, the DEVICE_ID is the most important, being a consistent and unique identifier used across the system to refer to a device. We also often use the TRADE_NAME, as it is more understandable to humans than DEVICE_IDs are. USAGE_CODE, PREF_NAME_CODE (PNC), and GMDN_CODE are various ways to group devices together, in increasingly precise ways, and RISK_CLASSIFICATION is used as a loose “risk level” of devices. The remaining fields are a pair of dates that we seldom use, a flag for whether the device is under the “new” regulations, and an “obsolete” flag, none of which we tend to use in our queries (but we could).
== DEVICE_ID ==
=== DEVICE_ID ===
DEVICE_ID is the primary key for this table. The Device ID is a grouping up of sometimes multiple model numbers into a group that one can think of as being the “device”; examples would be a type of breast implant, or a style and type of tubing made by a specific company. Here is a screenshot from the current (2022) MDALL, showing the various levels of the device hierarchy.
DEVICE_ID is the primary key for this table. The Device ID is a grouping up of sometimes multiple model numbers into a group that one can think of as being the “device”; examples would be a type of breast implant, or a style and type of tubing made by a specific company. Here is a screenshot from the current (2022) MDALL, showing the various levels of the device hierarchy.
At the time of writing, there were close to 300,000 device IDs in the MDS system. This is the main field used to link devices to incidents, to applications, and is often the unit we speak of when we talk about “a device”.
At the time of writing, there were close to 300,000 device IDs in the MDS system. This is the main field used to link devices to incidents, to applications, and is often the unit we speak of when we talk about “a device”.
=== ENTRY_DT ===
This is a date field, and captures when the device was added to the DEVICE table.
This is a date field, and captures when the device was added to the DEVICE table.
ENTRY_DT seems to first appear in 1996, presumably data was migrated from another system to the MDS for these earlier entries.
ENTRY_DT seems to first appear in 1996, presumably data was migrated from another system to the MDS for these earlier entries.
=== LAST_CHANGE_DT ===
Another date field, captures modification date of the record.
Another date field, captures modification date of the record.
This date also first appears in 1996, presumably when the MDS became operational.
This date also first appears in 1996, presumably when the MDS became operational.
=== PREF_NAME_CODE ===
This is a way to group devices that have similar function; it is referred to online in the ''Keyword Index to Assist Manufacturers in Verifying the Class of Medical Devices'' (<nowiki>https://www.canada.ca/content/dam/hc-sc/migration/hc-sc/dhp-mps/alt_formats/hpfb-dgpsa/pdf/md-im/keyword_motscles2-eng.pdf</nowiki>)
This is a way to group devices that have similar function; it is referred to online in the ''[https://www.canada.ca/en/health-canada/services/drugs-health-products/medical-devices/application-information/guidance-documents/guidance-industry-keyword-assist-manufacturers-class-medical-devices.html Keyword Index to Assist Manufacturers in Verifying the Class of Medical Devices].''
This system of classification essentially uses the FDAs product code groups and the USAGE code to come up with a 5-character code consisting of two digits (the USAGE_CODE) and three letters (resembling the FDA common product codes).
This system of classification essentially uses the [https://www.fda.gov/medical-devices/classify-your-medical-device/product-code-classification-database FDAs product code] groups and the USAGE code to come up with a 5-character code consisting of two digits (the USAGE_CODE) and three letters (resembling the FDA product codes).
An example PREF_NAME_CODE (PNC) would be 87QJK, which is decoded as CHAIR, PODIATRIC.
An example PREF_NAME_CODE (PNC) would be 87QJK, which is decoded as CHAIR, PODIATRIC.
The 87 here refers to the USAGE_CODE for orthopaedics, and QJK uniquely identifies podiatric chairs.
The 87 here refers to the USAGE_CODE for orthopaedics, and QJK uniquely identifies podiatric chairs.
The PNC is decoded by the table '''PREF_NAME_MAST''', but also has keywords on the table '''PREF_DESC_DTL'''. There are roughly 5,900 distinct PNCs assigned to devices.
The PNC is decoded by the table [[MDS PREF NAME MAST table|PREF_NAME_MAST]], but also has keywords on the table [[MDS PREF DESC DTL table|PREF_DESC_DTL]]. There are roughly 5,900 distinct PNCs assigned to devices.
This field is 100% populated, and appears on every record.
This field is 100% populated, and appears on every record.
The digits representing the USAGE_CODE on a PNC may not match the USAGE_CODE later on this table; it is not clear whether this is an error, or simply allows for flexibility. I find that the first two digits of the PNC are a better classifier in general, and it avoids selecting outdated USAGE_CODES that have been retired.
The digits representing the USAGE_CODE on a PNC may not match the USAGE_CODE later on this table; it is not clear whether this is an error, or simply allows for flexibility. I find that the first two digits of the PNC are a better classifier in general, and it avoids selecting outdated USAGE_CODES that have been retired.
As with many fields in the MDS, it is padded with white space, but as it is only 6 characters long and the PNCs are 5 characters it has little impact on presentation.
The TRADE_NAME is the name assigned to the device; it is just a string and is not necessarily unique.
=== TRADE_NAME ===
The '''TRADE_NAME''' is the name assigned to the device; it is just a string and is not necessarily unique. As with many fields in the MDS, it is padded with white space.
This field is populated with non-whitespace >99.9% of the time; there are currently (April 2022) 130 device entries with blank TRADE_NAMEs.
This field is populated with non-whitespace >99.9% of the time; there are currently (April 2022) 130 device entries with blank TRADE_NAMEs.
=== RISK_CLASSIFICATION ===
The RISK_CLASSIFICATION is the class of the device under the ''Medical Device Regulations''. The only valid options under the regulations are classes I, II, III, or IV; these are entered as the Arabic numerals 1,2,3, and 4 in the MDS system.
The '''RISK_CLASSIFICATION''' is the class of the device under the ''[https://laws-lois.justice.gc.ca/eng/regulations/sor-98-282/ Medical Device Regulations]''. The only valid options under the regulations are classes I, II, III, or IV; these are entered as the Arabic numerals 1,2,3, and 4 in the MDS system.
This field is 100% filled, but there are ~22,000 entries with a RISK_CLASSIFICATION of 0, which is not a valid RISK_CLASSIFICATION.
This field is 100% filled, but there are ~22,000 entries with a RISK_CLASSIFICATION of 0, which is not a valid regulatory class of device.
The term RISK_CLASSIFICATION appears on other tables, such as the '''PREF_NAME_MAST''' table; in this instance, it refers to the risk classification for the device ID, but on other tables, it may be referring to a group of devices in general or to a licence.
The term RISK_CLASSIFICATION appears on other tables, such as the [[MDS PREF NAME MAST table|PREF_NAME_MAST]] table; in this instance, it refers to the risk classification for the device ID, but on other tables, it may be referring to a group of devices in general or to a licence.
The risk classification on a device may not match the risk classification of the PNC on the '''PREF_NAME_MAST''' table; it is not clear whether this is an error, or simply allows for flexibility.
The risk classification on a device may not match the risk classification of the PNC on the PREF_NAME_MAST table; it is not clear whether this is an error, or simply allows for flexibility.
=== USAGE_CODE ===
The USAGE_CODE defines a broad group of associated devices. These categorizations are decoded by the '''USAGE_CODE_TAB''' table.
The USAGE_CODE defines a broad group of associated devices. These categorizations are decoded by the [[MDS USAGE CODE TAB table|USAGE_CODE_TAB]] table.
This field is populated >99.9% of the time with only 82 nulls, however 1% of the entries (3192) are assigned to the USAGE_CODE 00 which is “UNDEFINED” and ~7.5% (21983) belong to retired/old usage codes (these are marked by an asterisk in the decode table).
This field is populated >99.9% of the time with only 82 nulls, however 1% of the entries (3192) are assigned to the USAGE_CODE 00 which is “UNDEFINED” and ~7.5% (21983) belong to retired/old usage codes (these are marked by an asterisk in the decode table).
=== OBSOLETE_FLAG ===
I am not certain what this field is for, and have never used it. The field has the values N, Y, and null. Nulls make up ~258,000 entries, accounting for 88% of entries.
I am not certain what this field is for, and have never used it. The field has the values N, Y, and null. Nulls make up ~258,000 entries, accounting for 88% of entries.
=== NEW_REGS_98 ===
I am under the impression that this marks whether a device is subject to the new regulations, or was grandfathered in. This field is 100% populated with Y or N. ~39,000 devices are marked “N.”
I am under the impression that this marks whether a device is subject to the new regulations, or was grandfathered in. This field is 100% populated with Y or N. ~39,000 devices are marked “N.”
=== GMDN_CODE ===
The GMDN_CODE field was added to the table in 2018, and is the newest field on this table. The intent is to capture a code from the Global Medical Device Nomenclature (<nowiki>https://www.gmdnagency.org/</nowiki>) for devices moving forward, and to add these codes retrospectively to the system. Currently (April 2022) there are ~80,000 devices with non-0 GMDN codes (27%); a further 6,000 or so (2%) have only the number 0 (a default placeholder) and the remaining 208,000 devices (71%) are null. Of those assigned codes, 1,200 are either assigned obsoleted or non-existent codes.
The GMDN_CODE field was added to the table in 2018, and is the newest field on this table. The intent is to capture a code from the [https://www.gmdnagency.org/ Global Medical Device Nomenclature] for devices moving forward, and to add these codes retrospectively to the system. Currently (April 2022) there are ~80,000 devices with non-0 GMDN codes (27%); a further 6,000 or so (2%) have only the number 0 (a default placeholder) and the remaining 208,000 devices (71%) are null. Of those assigned codes, 1,200 are either assigned obsoleted or non-existent codes.
There are currently ~7,800 distinct GMDN_CODEs entered, out of a list of nearly 30,000 possible GMDN codes. As this is too extensive to provide decodes for in this document, users are invited to download these lists directly from the GMDN site should they need to decode this field.
There are currently ~7,800 distinct GMDN_CODEs entered, out of a list of nearly 30,000 possible GMDN codes. As this is too extensive to provide decodes for in this document, users are invited to download these lists directly from the [https://www.gmdnagency.org/Terms/Downloads GMDN site] should they need to decode this field.