Postgres Lookup Table for coded data

Question

I have a lot of data that is coded, and I'm trying to make lookup tables to add the description for the code. Is it possible to have a query automatically return the lookup table description if the primary and foreign keys are set, or will you always have to use a join in the query? Here are the two tables I have right now to test out the first lookup table.

Table "public.severity_lookup"
    Column     |         Type          | Collation | Nullable | Default
---------------+-----------------------+-----------+----------+---------
 severity      | character varying(1)  |           | not null |
 severity_desc | character varying(30) |           |          |
Indexes:
    "pk_severity_lookup" PRIMARY KEY, btree (severity)
Referenced by:
    TABLE "crashes" CONSTRAINT "fk_crashes_severity" FOREIGN KEY (severity) REFERENCES severity_lookup(severity)




Table "public.crashes"
          Column           |          Type          | Collation | Nullable | Default
---------------------------+------------------------+-----------+----------+---------
 crash_id                  | character varying(31)  |           |          |
...
severity                  | character varying(1)   |           |          |
Foreign-key constraints:
    "fk_crashes_severity" FOREIGN KEY (severity) REFERENCES severity_lookup(severity)

(I abbreviated the crashes table since it's long)

Here is the data in the severity_lookup table:
 SELECT * FROM severity_lookup;
 severity |    severity_desc
----------+----------------------
 F        | Fatality
 I        | Injury
 P        | Property Damage Only
(3 rows)

Maybe what I'm trying to do just isn't possible, but I've been looking around and haven't found any good information about it. If anyone knows of a resource/tutorial about lookup tables in SQL/postgres I'd appreciate the info.

Answer 1

For the following question above to be able to lookup data of the two table you can create views that joins the two tables and returns the description and the coded data and a sample query to create views can be as follows:

CREATE VIEW crashes_with_descriptions AS
SELECT c.crash_id, c.severity, sl.severity_desc
FROM crashes c
JOIN severity_lookup sl ON c.severity = sl.severity;

After creating the view you can query the above view with following:

SELECT * FROM crashes_with_descriptions;

The query above will return a result set with crash_id, severity, and severity_desc.