CODEWITHSUNDEEP
phpdatabasekey-value-store
benjamin.cohen-solal
benjamin.cohen-solal

Reputation: 481

List of values : Code constants or database?

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?

Upvotes: 13

Views: 3961

Answers (5)

Pepe Alvarez
Pepe Alvarez

Reputation: 1624

What works for me is DB only because I tend to do database first approach when designing backend systems.

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

id := generated database id
logType := varchar indicating the full name of the constant (i.e. "LOGTYPE_CREATION")
code := varchar representing a unique short length name or alias for quick and easy use in code (i.e. "LCR")

In this case this kind of constants might not be used in another relationships in your DB design, if that so, I would opt for the pure code base approach. But if they might appear on a relationship in the future then the DB approach would be the best for me.

Upvotes: 0

HZhang
HZhang

Reputation: 183

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.

Upvotes: 3

nemenems
nemenems

Reputation: 1092

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"

Upvotes: 5

Neville Kuyt
Neville Kuyt

Reputation: 29629

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.

Upvotes: 3

deceze
deceze

Reputation: 522155

More or less unstructured thoughts:

  • You should not use magic numbers, neither in your code nor database. As such, having log types "1", "2" etc. should be avoided.
  • You should use constants in your PHP code, and I'd give those constants meaningful string values, not numeric values.
  • Those string values can be used as the logType in the database directly.
  • Using an ENUM for the logType column therefore also makes perfect sense. If you want to add log types, you need to alter the table... so what? That's not something that should happen very often, you need to alter your code anyway to add a new constant, and you should have schema migration code in place anyway that makes it trivial to add a new SQL enum value as well.

So, this seems the best solution to me:

  • constants like LogTypeValues::CREATION = 'creation'
  • logType column of type ENUM('creation', ...)
  • schema migrations to handle addition of log types

Upvotes: 1

Related Questions