Should core application configuration be stored in the database, and if so what should be done to secure them?

Question

I'm writing an application around a lot of hierarchical data. Currently the hierarchy is fixed, but it's likely that new items will be added to the hierarchy in the future. (please let them be leaves)

My current application and database design is fairly generic and nothing dealing with specific nodes in the hierarchy is hardcoded, with the exception of validation and lookup functions written to retrieve external data from each node's particular database. This pleases me from a design point of view, but I'm nervous at the realization that the entire application rests on a handful of records in the database. I'm also frustrated that I have to enforce certain aspects of data integrity with database triggers rather than by foreign key constraints (an example is where several different nodes in the hierarchy have their own proprietary IDs and I store them in a single column which, when coupled with the node ID can be used to locate the foreign data).

I'm starting to wonder whether it may have been appropriate to simply hardcoded these known nodes into the system so that it would be more "type safe" and less generic.

How does one know when something should be hardcoded, and when it should be a configuration item? Is it just a cost-benefit analysis of clarity/safety now vs less work later, or am I missing some metric I should be using to determine whether or not this is appropriate.

The steps I'm taking to protect these valuable configurations are to add triggers that prevent updates/deletes. The database user that this application uses will only have the ability to manipulate data through stored procedures. What else can I do?

Answer 1

You are on the right track. There is a bit of cost-benefit. The other considerations are maintenance and complexity.

Complexity: Any dynamic hierarchy is going to be more complex than a static one. This involves more coding, testing, because there are many more possible failure cases. If you do not currently have a need to update the hierarchy, perhaps the complexity is not worth it? We often over-optimize towards complexity because we are anticipating changes in the far future. Which leads me to maintenance...

Maintenance: Think of the use case for change in your hierarchy. What external event would trigger the need to maintain the hierarchy and who would be the person making sure this happens? Does this change need to be backed-up, audited, versioned, approved, staged, etc? Source control systems provide a lot of these features.

So for example, if the hierarchy represents the business units of a company, and your use case is a far future corporate merger, then it would be very reasonable to assume that a static hierarchy in an HR system could be updated by programmers. In fact, in a merger maybe the whole system could get tossed out (!).

On the other hand, if the hierarchy is a product catalog, we all know that marketing people re-ogranize and refine this data regularly. And furthermore if they are told there will be a 2 week software project to recode, test and redeploy the catalog each quarter, I am pretty sure they are not going to be happy with the design. Thus a dynamic, DB-driven model makes sense.