Implement a virtual tag on the database level

Question

I have a set of objects, for which users manually applied tags, whatever they invented. I'd like to implement a task engine, where each task might be applied to objects from a tag. For convenience, I'd like to have an additional virtual tag "All objects" to have a task be applied to all objects regardless of tags.

A single task might require two (or more) sets of objects, e.g. "notify me about changes in all documents from tag X that were published on website tagged Y".

I see three ways of implementing this requirement:

1. Have a separate user interface and business code paths for the "All" case. This could be feasible for simple cases, but in my case it leads to having four cases for the example above, and potentially even worse in other cases.

2. Have a user interface that treats the "All" case like a normal tag (except maybe for making it impossible to remove items from the tag), and have business code add the tag automatically every time I list tags in the app, or have special cases for the "All" tag every time I handle a tag.

3. Have a user interface and business code handle the "All" case like a normal tag, and implement the virtual "All" tag on the database level. That would mean using database views that would add the "All" tag to the table of tags, and another view that would add the "All" tag to the m2m relationship listing which objects have which tags.

The third solution looks the most sane to me, but I don't have that much experience with programming on the database level. I can see the pros and cons of the first two solutions. What are the issues I might encounter if I chose to implement the third solution?

Answer 1

It seems to me like you're putting the cart a bit before the horse, especially since you have a partial implementation already. Take a step back and ask yourself what "all" means and what purpose it serves.

In this case it means "without conditions or filters", and the purpose is to provide a way for the user to tell the system "I don't care what the tags are". Its meaning becomes relevant at the point where you examine the actual value of the tags, which doesn't happen until you get to the DB query. Before that, the value of the tag doesn't matter. You can just pass it through your business objects as normal.

all documents from tag X that were published on website tagged Y

I'd write such a query like this (pseudo-SQL, proper syntax, e.g. ON, omitted for brevity):

FROM documents
INNER JOIN tags 
INNER JOIN publishedOn
INNER JOIN websites
WHERE tag = X AND website = Y

If X is "all", then I'd do it like this:

FROM documents
INNER JOIN publishedOn
INNER JOIN websites
WHERE website = Y

Tags are not queried because they aren't relevant. The user selected "all" which means "I don't care what tag the document has". Here, you make a decision at the data access layer and choose the second query if you see that. If you'd rather keep your DAL simpler and make the decision within the SQL query itself, that's easy to do:

FROM documents
INNER JOIN tags 
INNER JOIN publishedOn
INNER JOIN websites
WHERE (X = all OR tag = X) AND website = Y

If you use a query like this, you might run into the "magic number" problem, meaning "what value do I compare X against to see if it means all?" A common pitfall is using a hardcoded literal for comparison. For example, @tagID = -1. This is bad. What you've described doing - adding "all" as a "virtual tag" - is a way to prevent this problem, but it's far complicated than is necessary.

Again, ask yourself what the meaning of "all" represents in relation to your tags. It's "I don't care" or, more precisely, "unspecified". There's a value for that already: null. If the user picks "all", then set the tag filter property on your BO to null. When it's time to inspect the filter, if it's null, don't filter. The choice between performing the inspection in the DAL or DB is a question of where you want to put the little bit of extra complexity (which implies a minor maintenance increase and a performance hit which ought to be negligible).

Bottom line: don't treat "all" as a tag because it isn't one. Trying to crowbar it in will make things difficult and confusing for whoever maintains the code in the future.

Answer 2

You can potentially mix 1 and 2 but create less work in the process.

I would initially build your query logic on the assumption that all requests would be of the form "show all documents published on all websites" and then extend it to support filtering by tags (adding parameters to queries is easier than taking them away). You'd achieve this by simply checking whether the user had selected a tag for that object and adding a "where" clause (or whatever equivalent for your chosen DBMS) if they have.

From a UI perspective the "All" tag would just be an visual indicator that no tag filters are being applied.