I've got an optional column (residential) in one of my tables that is giving me trouble. If a Licensee intends to work w/ residential customers, they need to take an additional course (course_topic_code C):
CREATE TABLE Licensees ( license_num INTEGER PRIMARY KEY, license_level VARCHAR(50) NOT NULL, residential BOOLEAN ); INSERT INTO Licensees VALUES (123, 'Salesperson', NULL), (234, 'Salesperson', TRUE), (567, 'Salesperson', FALSE); CREATE TABLE Course_Requirements ( PRIMARY KEY (license_level, residential, course_topic_code), license_level VARCHAR(50) NOT NULL, residential BOOLEAN, course_topic_code CHARACTER NOT NULL, required_minutes INTEGER NOT NULL ); INSERT INTO Course_Requirements VALUES ('Salesperson', true, 'C', 90), ('Salesperson', true, 'A', 180), ('Salesperson', false, 'A', 180), ('Broker', true, 'C', 90), ('Broker', true, 'A', 180), ('Broker', false, 'A', 180);
The trouble is, it's valid for me to not know whether a Licensee intends to service residential customers. If the column is true they're required to take that course, but if it is false or null they're not required. This is the query I've come up with to see required courses for each Licensee:
select l.license_num, cr.course_topic_code, cr.required_minutes from Course_Requirements cr join Licensees l on cr.license_level = l.license_level and cr.residential = l.residential;
As you can see, the licensee w/ NULL for residential has no required courses:
567;"A";180 234;"C";90 234;"A";180
I might be able to devise a query that will give me what I want, but I feel that I might be modeling things incorrectly. I generally try to avoid nullable fields, but in this situation it seems appropriate. Is there a better way to model this?