List of values : Code constants or database?

My favorite solution would be:

Stores log types in database:

CREATE TABLE logTypes (    id (SMALLINT, PK)    code VARCHAR(32) UNIQUE    ) 

Create constants in code

class logTypes {    const CREATE_USER = 1;    const EDIT_USER = 2;    ...}

and choose a sync policy:

• Create the logTypes class from DB => the code is a "cache of the db", if cache is not generated the project won't work
• Create the DB from the code => The DB is only usefull for sql reports and applying SQL features to logtypes search, etc...

Example:

SELECT * FROM logs JOIN logTypes USING logtype_id WHERE logType LIKE "% CREATION"

This is a great question - and reasonable people will have different opinions, depending on your definition of "good".

Option 1 makes the PHP work nicely, but means that when you want to query the log, you need to have the constants in two places - typically, the log viewer is not the same application as the log writer. And you have to write some custom logic to translate your integers in the database into human-readable strings. If you have more than one application writing to the log database, keeping the constants in sync between them all becomes important - if application 1 thinks logType 1 = "User error" and application 2 thinks it's "system error", the whole thing falls apart.

Option 2 faces the opposite problem - how does your PHP code "know" that the database believes logType 1 to be "user error"? You definitely do not want to have magic numbers in the PHP codebase.

Option 3 notionally keeps both systems in sync - but now you have to find a way of synchronizing the database with your PHP constants file. There are a variety of logical routes to do this - but none are trivial.

As I am lazy and paranoid about bugs creeping in, I usually don't use integers for this - instead, the PHP code writes a string representing the log type to the database, from a constants file.

I also faced the similar situations a few times. Obviously, there are pros and cons of all those options mentioned above, and I haven't made my mind either. That's why I found this thread.

So far my preferred way is to use option 1, i.e., only storing constants in the application's source code (PHP/Java etc.). Its main advantage is to reduce unnecessary DB hits. Although some of you may say it's very small, but DB connection is often the bottle neck of lots of web applications. The other advantage is that it reduced the programming complexity.

The only thing I did in addition to what has been mentioned in this thread is to add a note to the comments in both the application source code and the related DB table columns to cross refer each other. This way, I tried my best to remind future developers to sync these two places if there is any change due to happen.