Highest Specificity Query - Database Query Pattern

I don't know a better name to call it, but a database query pattern I have used *A LOT* is what I call a 'highest specificity query'. The best way to describe it is to provide a simple example.

You have an application used by staff members in a hospital. All of the staff members use the application, but a configuration option in a central database is used to determine each user's level of security inside the application. The level of security, for the purpose of this example, is expressed as an integer value from 0-9, but could take any form required by the application. Since there are potentially thousands of users at each facility, you want to be able to configure a default value at the system level, and then override the value at the facility, department, and user levels. The application is designed such that a value specified at the user level would override department and facility settings and a setting at the department level will override a setting at the facility level.

I like to keep things simple, so I use a single table to host this configuration data. Check out the SQL Server view of the table configuration to the right.

Here is how the SQL table works. If you want to make an assignment at a facility level, set only the facility ID and securityLevel for the row (leave departmentID and userID Null) - any users associated with that facility that don't have a more specific securityLevel setting (set at the department or user level) will get that securityLevel value. Similarly, the securityLevel can be set for all users of a department by setting the facilityID and departmentID (leave the userID NULL). Check out the sample table below.

• rowID 1 provides a base security level for any users who aren't configured with a more specific securityLevel vaule.
• rowID 2 overrides rowID 1 and sets a security level of 1 for all users of facilityID 1. In this example, this would provide a default securityLevel for all users at the facility of 1. A security level of 1 is a low level that might provide only read-only access to non-sensitive information.
• rowID 3 overrides rowIDs 1 and 2 and sets a security level of 5 for all users of department 20 inside facility 1. This might represent a clinical department that requires write access to clinical areas of the application.
• rowID 4 overrides rowIDs 1, 2, and 3 and sets a security level 3 for user 345 inside departmentID 20 and facilityID 1. In this case, a new employee at the facility has a lower level of access than everyone else in the department.
• rowID 5 overrides only rowID 1 and sets a very high securityLevel for facilityID 2. This facility asked to keep security simple and give everyone a high level of access.

The SQL query used is shown below. It is written with the assumption that the query is running inside a stored procedure with parameters named @p_facilityID, @p_departmentID, and @userID. When the query is run, the values for facility, department, and user are provided by the calling application, and only the most specific row is returned from the query. Take a look at the query below.

SELECT TOP 1 securityLevel 
FROM SecurityLevels 
WHERE 
	(facilityID = @p_facilityID OR facilityID IS NULL) 
	AND 
	(departmentID = @p_departmentID OR departmentID IS NULL) 
	AND 
	(userID = @userID OR userID IS NULL)
ORDER BY 
	userID DESC, departmentID DESC, facilityID DESC

Some notes about the query:

• TOP 1 is included to force the single most specific row to be returned by the query.
• The mildly complex WHERE clause filters rows where the row either matches the value for the field or is set to NULL. This allows instances where none (or perhaps only a portion) of the criteria match rows in the table. For example, using the example table above, suppose a query is run where the following parameter values are provided: facilityID = 2, departmentID = 100 and userID = 300. The WHERE clause will return only the row where rowID = 5, because it was the only row where even a partial match of the search criteria was found.
• The ORDER BY clause puts the most specific row at the top of the results (the TOP 1 ensures that only the results from this row are returned). In the example, we want settings at the user level to override any other settings. The priority then falls to department and then facility. Therefore, we sort in the order of userID, departmentID, facilityID. The DESC argument ensures that non-NULL values sort ahead of NULL values, ensuring that the top record in the result set is the most specific row for the given search criteria. In the example table above, searching using facilityID = 1, departmentID = 20, and userID = 214 would return rowID 3, since that row is the best match.

You probably noticed that I used a NULL value to represent a value that isn't assigned in the table. It's also possible to use a zero for unassigned values. It's a trade-off either way: if you use NULL values then you need a surrogate primary key since NULLable columns are not candidates for primary keys. My example above uses rowID for the surrogate key. However, if you use zeros then you either can't use referential integrity or you need to add bogus rows (with zero values) to the primary key tables.

This type of table and the highest specificity SQL query that goes with it has been an indispensable pattern in building enterprise software solutions. In the example above, I used only 3 fields for search criteria (facilityID, departmentID, and userID), but the pattern supports any number of search criteria necessary for your needs - simply add the columns to the table and modify the SQL query accordingly. Further, since the bulk of the configuration in these tables will be in the higher-order fields (that's been my experience) such as facilityID or departmentID, the tables remain very small even for very large implementations and perform very well.