Code tables are used to track “codes” throughout the database. For example, program codes, zip codes, state codes, special ed codes, enrollment codes, etc. All code tables follow a similar convention:

  • They exist in the codes. schema
  • They all have the code_id, code_key, code_translation, and system_key columns
  • They can be managed via a system administrator in the Code Management module.

System Keys

The system_key column is how we can identify specific codes from within our code. The column cannot be changed via the UI and so it is safe to rely on. I recommend you use them as well if you are looking for a specific code. For example,

-- Get only lunch programs
SELECT code_id
FROM codes.student_programs
WHERE system_key IN ('lunch_free_1', 'lunch_free_reduced')

How to find references

pgAdmin (and psql) have the ability to show you which tables reference the primary key of another table. You can use this to find where your code table is being referenced. For example

Table Dependents

In this case you can see that codes.dna_scopes is referenced in assessments, repositories (summary assessments), and skill assessments.

Want some more examples? Send me some suggestions of the data you would like to see zach@illuminateed.com