Postgresql – constraint view CASE values

postgresql

Below is a sscce to motivate the case.

I keep a table of persons:

CREATE TABLE person (name VARCHAR);
ALTER TABLE person add PRIMARY KEY (name);
INSERT INTO person (name) VALUES ('Bob'), ('Mike');

… and the encounters these persons have had with the law:

CREATE TABLE infraction (person VARCHAR, crimetype VARCHAR);
ALTER TABLE infraction ADD CONSTRAINT infraction_2_person FOREIGN KEY (person) REFERENCES person(name);

INSERT INTO infraction (person, crimetype) VALUES('Bob', 'MISDEMEANOR'), ('Bob', 'MISDEMEANOR'), ('Bob', 'FELONY');

Where crimetype in the table above would normally be a FK into a master table of crime types (omitted here to keep things short).

So Bob has committed two misdemeanors and one felony whereas Mike has a clean record.
The above two tables capture all the information I need.

Now I create a view that produces an overall assessment of that person, depending on the crime types they've committed and how many of them:

CREATE VIEW person_assessment AS
SELECT name, CASE
           WHEN NOT EXISTS(SELECT 1 FROM infraction b WHERE b.person = a.name)                          THEN 'exemplary'
           WHEN     EXISTS(SELECT 1 FROM infraction b WHERE b.person = a.name) AND 
                NOT EXISTS(SELECT 1 FROM infraction b WHERE b.person = a.name AND b.crimetype='FELONY') THEN 'relatively law-abiding'
           WHEN     (SELECT COUNT(*) FROM infraction b WHERE b.person = a.name AND b.crimetype='FELONY')<=4 THEN 'felon'
           WHEN     (SELECT COUNT(*) FROM infraction b WHERE b.person = a.name AND b.crimetype='FELONY')>5 THEN 'career criminal'
           ELSE 'unhandled case'
          END AS citizen_type
FROM person a;

The above view produces:

name citizen_type 
---- ------------ 
Bob  felon        
Mike exemplary

My question are:

  1. is the SQL code for the view above idiomatic or is there a better way to write it?
  2. is there a way I can somehow constraint the citizen_type that the view produces so that I can make it reference a master table of citizen types with descriptions, etc. I somehow want to make it explicit that the values relatively law-abiding, felon and career criminal come from a controlled set of strings that other tables can cross-reference and I can assign some other properties on them (description for instance).

Best Answer

You could add a column significance to your crimetypes table, with increasing values for the crimes (ie. 1 for INFRACTION,2 for 'MISDEMEANOR', 3 for 'FELONY', etc.)

Then your query could be written as:

CREATE VIEW person_assessment AS
SELECT p.name, 
       CASE COALESCE(MAX(c.significance), 0)
           WHEN 0 THEN 'exemplary'
           WHEN 1 THEN 'law-abiding'
           WHEN 2 THEN 'relatively law-abiding'
           WHEN 3 THEN CASE WHEN COUNT(CASE WHEN c.significance >= 3 THEN 1 END) <= 4 
                                THEN 'felon'
                                ELSE 'career criminal'
                       END
                  ELSE 'unhandled case'
       END AS citizen_type
FROM person AS p 
  LEFT JOIN infraction AS i 
    ON i.person = p.name
  LEFT JOIN crimetypes AS c
    ON i.crimetype = p.crimetype 
GROUP BY p.name ;

This is more of a way to have the query "tidied up", readable and easy to change in the future.

It may not be as efficient as your query though, depends on the data. If no person in the database has any crime stored, only the first WHEN of your case has to be run. If on the other side, most persons in the database have felonies, several correlated subqueries will have to be run so the above strategy will probably be more efficient as it uses only a join and a GROUP BY.

About question 2, you can always join this view to another table with descriptions.

SELECT 
    ....
FROM person_assessment AS pa
  JOIN citizen_types AS ct
    ON ct. citizen_type = pa. citizen_type ;
Related Question