19

A long-standing question for me has been: when do I store data (actual values) in a database table, and when do I store them right in the code?

The untold consensus has typically been as such(*):

If it is a single variable or a simple structure, or an array of a few values, put data right in the code.

[* consensus has been debated in comments and answers, but basically I wanted some kind of a premise to jump-start the question, so feel free to challenge and improve upon it]

Example:

$number = 44;
$colors = array("blue", "yellow", ... "mauve");

If it has hundreds+ of rows of data of the same type, use a database.

But there seems to be a gray area; what about cases that are not so clear? What considerations and factors does one need to pay attention to in order to make a decision?

Example:
Say your company uses 10-15 different types of motors frames that can be represented as "412T". You have about 30 of them and they change rarely. You can either create a DB table for those or hard-code them in a database. In this case, motors are static, physical things that are not likely to change often.

Keeping them in code subjects them to source control, where in a database, DB changes are typically not tracked. But keeping them in a database frees up (separates) code from data.

Another (actual) example I can use is this question of mine: https://stackoverflow.com/questions/26169751/how-to-best-get-the-data-out-of-a-lookup-table (currently 48 rows of option data).

Community
  • 1
Dennis
  • 8,157
  • 5
  • 36
  • 68
  • 3
    The untold consensus has typically **NOT** been as such: If it is a single variable or a simple structure, or an array of a few values, put data right in the code. – Pieter B Oct 10 '14 at 10:45
  • There is a middle way: Data is stored in a database. Then it is extracted to write source code (e.g., in a `global_constants.h` file). – mouviciel Oct 16 '14 at 07:55
  • @mouviciel but what constitutes data... you need some data to access the database, so you can't store all data in there. – jwenting Oct 16 '14 at 12:48

8 Answers8

34

I don't think these two statements really represent a consensus about when to hard code data:

If it is a single variable or a simple structure, or an array of a few values, put data right in the code

If it has hundreds+ of rows of data of the same type, use a database

Simple counter-example (I am sure there are better ones): programming language syntax tables are complex large structures that are often hard-coded. Take a look at an example from the Perl source code.

Instead I would focus first on asking:

  • How often does the data change?

  • Who might need to change it?

If the answer to "how often" is "more often than I want to deploy a new version of my app," then you should not hard code the data.

If the answer to "who changes it" is "anybody besides the programmer," then you should not hard code the data.

In a small shop, it is possible the distinction between the coder and the user has disappeared, and the idea of "deployment" has also disappeared. In this case you are the king of your own domain and you can do whatever you like.

But even in that situation, the need to collaborate can come up, and that can be difficult if a custom application does not follow a convention that programmers typically do follow.

Community
  • 1
x-code
  • 1,058
  • 8
  • 11
  • 7
    Similar to the how often question, another question is, "How quickly does it need to change?" The gating factor may be how long it takes to deploy across your user base. For centralized server software, the answer may be minutes, but for mobile apps in the field, it could take weeks. – CuriousRabbit Oct 09 '14 at 22:57
  • In Perl example, I would put it as `an array with a few values`, few being 377-ish rows of fundamental data type. Maybe more than "a few" to some, but it is still pretty small. I have similar yet slightly less flat structures hardcoded. If it was a thousand+ rows I probably be more reluctant to hardcode it this way. – Dennis Oct 13 '14 at 13:53
14

I'd go with the third option: a config file!

For applications that I work on (in Java, so all my examples are use Java+Spring ), such values are usually stored in config files and injected (via Spring) into the code that needs them when the application starts up. In a properties file:

motorFramesString=412T, 413T, ...

In the spring config:

<bean="motorFrameManager" class="myCompany.MotorFrameManager" >
    <property name="motorFrames" value="${motorFrames}"/>
</bean>

The advantage of this is that you can change or add more of these mostly-static values easily enough, without a recompile, and you don't have to worry about filling your (relational) database with reference data (since that seems to be a concern, and maybe not everything needs to be in the database anyway).

As for why these values should go into a config file instead of a reference table: Do you plan to use these values mostly in code, or mostly in the database? If you have many existing queries and view and procedures that depend on these values, then it might be best to put them in the database as reference data, as it's easier than loading them from config files and sending them as parameters to every possible query/view/procedure that references them. If the values are mostly used in application code, then the config file is probably a better choice.

A more complicated example like what you link could be done too, with or with out properties.

In products.properties:

productA.name=Product A 123
productA.hasMotor=true
productA.numFeet=1
productA.hasOutlet=true
productA.needsManual=true

productB.name=Product B 456
productB.hasMotor=false
productB.numFeet=1
productB.hasOutlet=true
productB.needsManual=true

In your spring config file:

<bean name="productA" class="com.mycompany.Product">
   <property name="name" value="${productA.name}"/>
   <property name="hasMotor" value="${productA.hasMotor}"/>
   <!-- rest omitted for brevity -->
</bean>
<bean name="productB" class="com.mycompany.Product">
   <property name="name" value="${productB.name}"/>
   <property name="hasMotor" value="${productB.hasMotor}"/>
   <!-- rest omitted for brevity -->
</bean>
<!-- configure as many beans as needed -->
<bean="motorFrameManager" class="myCompany.MotorFrameManager" >
    <property name="motorFrames"> <!-- assumes that MotorFrameManager has a property motorFrames which is a List<Product> -->
        <list>
            <ref bean="productA"/>
            <ref bean="productB"/>
        </list>
    </property>
</bean>

The nice thing about this is that if your source data is a spreadsheet (like in the question you linked to) you can use macros in Excel to automatically generate the properties and spring snippets.

FrustratedWithFormsDesigner
  • 46,105
  • 7
  • 126
  • 176
  • while you are on the frame example, it is fairly simple (only one string value). Will your approach be basically the same for the options table that has n-tuples of mixed variables (linked at the bottom of my question)? – Dennis Oct 08 '14 at 18:20
  • @Dennis: Added a more complex example. – FrustratedWithFormsDesigner Oct 08 '14 at 18:54
  • 8
    A config file is just one form of db. – DougM Oct 10 '14 at 11:35
  • @DougM Exactly. I hate it when people equal DB with RDBMS or SQL. – musiKk Oct 10 '14 at 11:50
  • 3
    @DougM, I agree, but there is a world of difference between setting up a series of config tables in Oracle, and having a simple XML config file. The config file also has the virtue of being controlled by version control. The config file is a middle ground and one that encourages the coders to separate more config data out. I'm struggling to think of realistic scenarios where time this is not a good thing – mcottle Oct 16 '14 at 03:59
  • @mcottle fixed data entered into your RDBMS can be stored as a SQL script of insert statements. This can happily be version controlled. too. Frankly, if you need a single local file for config, you should be considering SQLite anyway. A XML config file is always a worse choice than a really simple "ini" file for simple stuff, or a DB for more complex stuff. – gbjbaanb Oct 16 '14 at 07:39
  • @gbjbaanb Yes, I am well aware of that but how do you know for certain that the script has been run and hasn't been updated by later changes? I worked at a place (or two) in the '90s that did this and I can tell you it's pretty hard to keep on top of >150 support scripts that are changing data in the database and grep through all of them looking for the script that made the change to the standing data... – mcottle Oct 16 '14 at 08:00
  • @mcottle nothingis a substitute for organisation. I worked at a place that would update the DB directly... and no-one quite knew what half the data in there meant after time. How do you deal with script-based input data? By dropping the DB and running the scripts to populate the test DB every time you release a new version. Its not difficult to keep on top of changes in there, especially if you have SCM history to tell you whats changed. And seriously, its much easier than having to do the same thing with data stored in XML files! – gbjbaanb Oct 16 '14 at 08:14
  • 2
    @gbjbaanb:For most applications a RDBMS is WAAAAAY overkill, even for otherwise quite extensive systems. Not to mention that they are slow and require a fair amount of effort to maintain and integrate with. "ini" files don't provide any type safety, you have to write the parser yourself and you have to write your own type checking. XML Config files, at least in .Net, get all the benefits provided by either of your preferred options essentially FOR FREE. So your assertion that a XML config file is always a worse choice couldn't be more wrong. – Dunk Oct 16 '14 at 15:26
  • @Dunk a ini file was a suggestion for the truly simplistic config files, anything greater than that is best served by an embedded SQL DB - SQLite is awesome, or Microsoft's LocalDB for example. XML might be a middle-ground, but I often see them turn into monsters, and you still have to write some parsing code for an XML schema, especially when you start to nest those elements. – gbjbaanb Oct 16 '14 at 17:20
  • @gbjbaanb: A framework like Spring *already* does the hard work of defining a schema, parsing the XML, and creating objects at runtime. There are some situations where it may be overkill and unnecessary, but I find there are many situations where it faster and easier to use Spring than to embed something like SQLite. – FrustratedWithFormsDesigner Oct 16 '14 at 17:42
10

I think the premise of the question is not quite right. The dividing factor is not the quantity of records that need to change, but the frequency of changes as well as who changes them.

Frequency

When data is volatile, in the sense that it changes often and outside of a release cycle of the software, it needs to be able to be configured outside of hard-coded values or even configuration files. A database makes sense here, especially if the application itself is capable of maintaining it.

Who

When a customer needs to be able to change data, it needs to be modifiable in a user-friendly manner and outside of a release cycle.

Common Thread

The common thread here is that when data needs to change outside of a software release, it should be stored in a database. Databases might be upgraded during a release, but the data lives on without being reset or heavily modified. When a customer needs to be able to modify data (or configure functionality), it should be stored in a database with a nice front end that is idiot-proof.

Testing

Be sure to write unit tests that validate your software in a variety of configurations. Maybe your customer turns on an optional prompt, or redefines one meter to equal twelve feet. Regardless of how reasonable the change is, if your software allows it, it damn well better validate that the change works as expected no matter how inane it is.

Community
  • 1
4

It is neither the frequency of changes nor the amount of data that decides where to store data.

If the data is required to run the program, it is part of the program code, so store it as a constant. All other data goes in the database.

Of course, config files, images, sounds, etc are usually better stored on the filesystem.

winkbrace
  • 353
  • 1
  • 4
  • I made a call-center-assist program that was entirely db driven; the data was required to "run the code", but it would have been absurd to compile it. – DougM Oct 10 '14 at 11:38
  • 1
    I was an Oracle developer for 8 years, but I still don't like applications that have such tight coupling with the underlying database. – winkbrace Oct 10 '14 at 12:02
  • "If the data is required to run the program, it is part of the program code, so store it as a constant.": thanks for this thought, but I think it misses some nuances. If "required" means "program crashing without the data" - then you can "store it as a constant", BUT you can also write a check for its presence and exit gracefully. If "required" means "program is useless without the data", then I disagree. Take a currency conversions calculator. Without a table of exchange rates it is useless. Should the exchange rates, changing e.g. daily, be hardcoded? I don't think so. – Jan Żankowski Jun 16 '21 at 20:34
2

If there's even the slightest chance you will ever get a phone call resulting in you having to rebuild an application because something hard-coded has changed, then don't hard-code it. At the very least keep it in a config file or db table. You don't have to provide any UI to maintain it necessarily, but dialling in and changing a config file or running an SQL UPDATE on a table is surely preferable to rebuilding the whole shooting match.

Alan B
  • 436
  • 3
  • 7
1

The distinction is indeed a somewhat gray area, but my approach to this kinds of issues is: does the data change in production"? Anything that changes after deployment in a production environment should go in the database, even for things that may rarely change.

So the question you should be asking is not "how often will it change?", but "can it change?". If a value for a property can vary within the same code iteration (without touching anything else in the code) on the production environment, it goes into the database.

Thyamarkos
  • 378
  • 1
  • 4
0

What you are also missing is "program control" type information say max buffer size, number of items aloud in an order, max page size for a screen is always better either hard coded in the program or in a configuration file.

If you keep this sort of data in a database there is always the possibility of someone changing it on the fly and completely changing the behavior of a system.

The other problem is that there is no easy way to get database entries through the source control/change management/automatic build systems that you should be using.

James Anderson
  • 18,049
  • 1
  • 42
  • 72
0

One thing you should never keep in the database are the details needed to access the database.
You've a bit of a catch-22 if you need to access the database to retrieve the connect strings, username, and password for that same database after all.

Hardcode it? hmm, might work if you're using some sort of database that installs and ships with the application.
Configuration file? More promising but what about account security?

Of course you could store the database information in that config file in encrypted form, but then you'd need to have the decryption keys stored somewhere, and that'd be hardcoded almost certainly.

Apart from that, there are things that never change. Things like the natural constants (G, pi, e, you name it), things maybe like certain regular expressions, like email validation.

jwenting
  • 9,783
  • 3
  • 28
  • 45