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');
crimetype in the table above would normally be a FK into a master table of crime types (omitted here to keep things short).
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:
- is the SQL code for the view above idiomatic or is there a better way to write it?
- is there a way I can somehow constraint the
citizen_typethat 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
career criminalcome from a controlled set of strings that other tables can cross-reference and I can assign some other properties on them (description for instance).