How to reconcile constants in code and in database

[poisa]

Let's take this status table:

id | name
---+-------
 1 | active
 2 | inactive
 3 | banned

In this case, I know that this table will not be edited ever, and if it is, it will happen extremely rarely and most likely a status will only be added. Also, the name is basically for when you look at the DB manually as it is never displayed anywhere.

 

Let's say a user table  has a status_id field (with a foreign key constraint to the status table, for good measure). Knowing the status of the user by looking at the database is pretty straight forward. But let's go to the code...

 

In PHP it's very obscure to do things like:

if ($user->status == 2) {
   // what the heck is 2?
}

So I usually do it this way:

if ($user->status == User::STATUS_INACTIVE) {
   // Ah, yes, user is indeed inactive.
}

The only problem is that it requires me to basically copy the contents of a table in code; anything in the status table I have to create as a class constant.

 

I'm curious to see how people deal with situations like these. I encounter this issue pretty often and would like to know if there are other ways to deal with this in a more elegant way.

[benanamen]

As far as status in particular, any code I have done is fairly obvious what 1,2, or 3 is by the code that follows it.

 

if ($user->status == 2) {
// This code should reveal pretty obviously what 1, 2, or 3 is by what it does.
}

[Psycho]

The only solution I can think of would require instantiating the class first and creating the properties dynamically. But, if you did add another status, I would think it would also require code changes anyway to determine different outcomes based on that status. So, changing a constant in a class is a very minor change in what would likely be a much larger coding effort.

[kicken]

I just do like you mentioned in your second example, create a class with constants to represent the different values. Most of the time I won't event have a table representing these values so there's no need to keep things in sync. If I need to look at raw database results I just pull up the class as a reference for what the values are, not really a big deal. If I need an ID to text translation then I just handle it in code with a mapping function. For example:

class AccountStatus {
    const PENDING = 1;
    const VALID = 2;
    const SUSPENDED = 3;
    const BANNED = 4;
    const DELETED = 5;

    public static function toText($status){
        switch ($status){
            case self::PENDING: return 'Pending';
            case self::VALID: return 'Valid';
            case self::SUSPENDED: return 'Suspended';
            case self::BANNED: return 'Banned';
            case self::DELETED: return 'Deleted';
            default:
                throw new \InvalidArgumentException;
        }
    }
}

Note this method only really applies to values that are essentially hard coded and not user configurable.

[gizmola]

I do the same as Kicken in most cases.  There are exceptions, where there is some significant benefit to having a static database lookup table.  If there is a way for the system to function purely through the addition of new lookup values, and not requiring code changes, then having the lookup table is great.

 

Alternatively, there are times in the past where I will utilize a lookup table using what I call a 'code' primary key.  This is typically a CHAR[1] with an associated name.  This does have some human readability advantages when looking at a raw table, if the codes are fairly clear.  In your example:

A| Active
I | InActive
B |Banned 
etc.

Make it easy to remember, and easy to look at the values in the user table and tell what is going on.  Since a char[1] is highly efficient use of storage, there's no big overhead cost or loss of functionality.    Sometimes people who don't know better think the only Primary keys they can or should use, should be integers with auto increment.

 

The other issue that gets brought up is the cost to read in those database values on a regular basis which is very wasteful.  People should be using caching solutions like memcached or Redis to avoid that overhead, if you choose to go that direction.

[ignace]

It boils down to good OO-design. My table would look like this:

 

account_status
--------------
status (PK)
--------------
pending
active
inactive
banned

And a matching AccountStatus class.

 

class AccountStatus {
const BANNED = 'banned';

private $status;

public function __construct(string $status) {
$this->status = $status;
}

// .. other getters

public static function ACTIVE() {
return new static(static::ACTIVE);
}

public function isBanned(): bool {
return $this->status === static::BANNED;
}

public static function getOptions() {
return [static::ACTIVE, static::INACTIVE, static::BANNED];
}
}

 

To query if the user is banned is then:

 

$user->getStatus()->isBanned();

 

Changing the user status:

 

$user->setStatus(AccountStatus::ACTIVE()));

[poisa]

Hmm... Interesting ideas. 

 

ignace, yours in particular reminds me of Java a bit. I do have a question about it though. 

 

In your getOptions() method you are returning strings. Do you see any benefit in doing the following instead?

public static function getOptions() {
    return [static::ACTIVE(), static::INACTIVE(), static::BANNED()];
} 

[kon]

As I can see you have in your status table id and name , as you wrote this table is rarely modified and I guess the name of status (description) is also unique. With all that instead of evaluating the status id , you could evaluate the status name , making your code easy to read and meaningful. With some way you have this status in your user object , you can keep that and make its value to be the status name or even split it in statusId and statusName (where are the accessors and mutators ?).

 

 

So the code:

if ($user->status == "inactive") 
{
   // now everyone can understand it
}

Edited October 13, 2017 by kon