MDS DEVICE table

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:

Fields on the MDSDB.DEVICE table

Field name	Data Type	Nullable?	Default	Decode
DEVICE_ID	NUMBER	No
ENTRY_DT	DATE	Yes
LAST_CHANGE_DT	DATE	Yes
PREF_NAME_CODE	CHAR(6 CHAR)	No		PREF_NAME_MAST
TRADE_NAME	CHAR(150 CHAR)	No
RISK_CLASSIFICATION	NUMBER	No
USAGE_CODE	CHAR(2 CHAR)	No		USAGE_CODE_TAB
OBSOLETE_FLAG	CHAR(1 CHAR)	No
NEW_REGS_98	CHAR(1 CHAR)	No	'Y'
GMDN_CODE	NUMBER	Yes		no decode available in system

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 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) Medical Devices Active Licence Listing (MDALL), showing the various levels of the device hierarchy.

Screenshot from the MDALL illustrating the many-to one relationships between device identifiers, device IDs, and licences.

As you can see in this example, the licence is for SALINE-FILLED MAMMARY PROTHESIS – SMOOTH, but it has at least two DEVICE_IDs included, that were issued at different times. Each of these DEVICE_IDs then has many models numbers, chiefly differing in this case in the size of the implant. For tubing or screws, as another example, device identifiers may be the length of the tubing or screws.

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.

~88% of rows of the table have an entry date (~34,000 rows are null for ENTRY_DT)

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.

~94% of rows of the table have an entry date (~14,000 rows are null for LAST_CHANGE_DT)

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.

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 product codes).

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

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.

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.

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.

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.

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

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.

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

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