List of values : Code constants or database?

Question

I wanted to know if there was a good practice for list of values management. For example I have a database table logs as following :

---------------
| logs        |
|-------------|
| id          |
| idLogType   |
| message     |
| date        |
---------------

My question is about the field "idLogType". I know that an enum is not the good way to handle this kind of field because if I want to add new values I have to ALTER the table. So I'm going to create a list of values.

What is your recommandation to handle list of values ?

1. Only with PHP constants?

class LogTypeValues {
  const LOGTYPE_CREATION = 1;
  const LOGTYPE_EDITION  = 2;
  const LOGTYPE_DELETION = 3;

  private $_logTypes = array();

  public function __construct() {
    $this->_logTypes[self::LOGTYPE_CREATION] = "Creation";
    $this->_logTypes[self::LOGTYPE_EDITION]  = "Edition";
    $this->_logTypes[self::LOGTYPE_DELETION] = "Deletion";
  }

  public function getId($logType) { ... }
  public function getLogType($id) { ... }
}

$request = $pdo->prepare('INSERT INTO logs SET idLogType = :idLogType, ...');
$request->execute(array('idLogType' => LogTypeValues::LOGTYPE_CREATION));

2. Only with database ?

------------
| logTypes |
------------
| id       |
| logType  |
------------

<?php
$request = $pdo->prepare('INSERT INTO logs SET idLogType = 1, ...');
$request->execute(array());

3. In database but also with PHP constants ?

------------
| logTypes |
------------
| id       |
| logType  |
------------

class LogTypeValues {
  const LOGTYPE_CREATION = 1;
  const LOGTYPE_EDITION  = 2;
  const LOGTYPE_DELETION = 3;
}

What do you thing about these 3 methods?

Answer 1

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.

Answer 2

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"