4

In my application, I have a lot of SQL queries that have business logic embedded in them. For example, in searches I 'hide' results that are low ranked, less than -5 'community rating' because those results contain spam, inaccuracies, etc and have been flagged by the community.

In my SQL query, it might look like:

SELECT
    ...
    ...
WHERE community_rating > -5

Here, I'm embedding business logic right into the query. What if we change our minds later about the threshold for hiding results? I have 4 queries right now that use this magic number, and more might be coming.

My gut says to pull these numbers out into a 'database config file', because the numbers are reused a lot and can be changed, but I'm tempted to say no because it adds a lot of confusion as you add more $-type variables like:

SELECT
    ...
    ...
WHERE community_rating > $5

And I would have an array of values to pass to the query like:

[..., ..., ..., ..., DB_CONFIG.HIDE_RESULTS_AT_RATING]

I imagine that it would get even sloppier and hard to understand over time, but that's probably inexperience talking.

Should one pull database-level business logic out to a configuration file?

Edit: This is the backend to a web service. This isn't a parameter that a user who is doing a search can change. In all queries, results are excluded at this threshold, and the threshold is decided by me. There are multiple search functions, all of which use this threshold.

  • PS - I couldn't figure out how to tag this accurately; if anyone has tags to add/remove, be my guest! – Chris Cirefice Jul 12 '15 at 16:48
  • Tags look okay to me. But what kind of application are we talking about? If this is the backend to a webservice, I'd suggest optional parameters in the service request (with documented defaults), but if this is all part of a single native code app with no networking then we'd be talking about constants in code versus a config file. – Ixrec Jul 12 '15 at 17:03
  • @Ixrec Right, I should have clarified. This is the backend to a web service. However, this is not a parameter that a user can change, it's a business rule I decided on to essentially hide spam and other nonsense. The user sends a request with the search parameters, but all results in all queries are hidden at this threshold, regardless of what the user searches for. – Chris Cirefice Jul 12 '15 at 17:07
3

Config files should almost be the last places to put business rules or parameters. Config files should in general used only for administrative functions - things that DB admin or IT admin would control - certainly not the business logic.

One of the key reasons why config files for business logic is bad is because typically business rules not only change in values but they evolve from simple to complex. They can grow from being a list of attributes to become a tree of policies and then a lot of procedural code will get added to process them in support scripts which becomes badly maintained.

Another aspect to remember is that when you take $5 - do you expect to change while the server is running? It does depend on your exact implementation, but how often do you want to keep loading and parsing config files? and if that policy value changes, how do you communicate that policy needs to be reloaded?

A much better place to keep parameters is DB itself! You can have one of the CONFIG_TABLE where each attribute (column in DB table) is one specific parameter. It can be simple one table or it can get nested with multiple tables. You can have different policy profiles - which are just different rows of this table.

If through some other interface of the application, the parameters can even be changed if required and the very next query that will fire will automatically incorporate it.

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.