Below is a sscce to motivate the case.

I keep a table of persons:

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

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

FROM person_assessment AS pa
  JOIN citizen_types AS ct
    ON ct. citizen_type = pa. citizen_type ;
| improve this answer | |
  • I read that as an answer to question #1, as regards #2 is there a way to constraint the citizen_type values? – Marcus Junius Brutus Feb 21 '14 at 17:59
  • See my edit but I'm not sure if that is what you mean. – ypercubeᵀᴹ Feb 21 '14 at 18:06
  • I understand that you can join views and tables. The thing is that there is no way to have those strings only in a table. They have to be present both in a reference table and in the view and you have to visually scan the text of the view to confirm that they are the same, and that they are all used, otherwise the joins will fail and the view is not going to alert you to that. – Marcus Junius Brutus Feb 21 '14 at 18:10
  • We can change the view to use this reference table. The problem with that is the <=4 and >4 cases. – ypercubeᵀᴹ Feb 21 '14 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.