Database design: Matching sql database keys to php constants?

Question

Well this is a simple design question I've wondered about many times and never found a satisfying solution for. My example is with php-sql, but this certainly applies to other languages too.

I have a small database table containing only very few entries, and that almost never needs updating. eg this usertype table:

usertype_id (primary key)  | name       | description
---------------------------+------------+-------------------
1                          | 'admin'    | 'Administrator'
2                          | 'reguser'  | 'Registered user'
3                          | 'guest'    | 'Guest'

Now in the php code, I often have to check or compare the type of user I'm dealing with. Since the user types are stored in the database, I can either:

1) Select * from the usertype table at class instantiation, and store it in an array.
Then all the ids are available to the code, and I can do a simple select to get the rows I need. This solution requires an array and a db query every time the class is instantiated.

$query = "SELECT info, foo FROM user WHERE usertype_id = ".$usertypes['admin'];

2) Use the name column to select the correct usertype_id, so we can effectively join with other tables. This is more or less equivalent to 1) but without needing to cache the whole usertype table in the php object:

$query = "SELECT info, foo FROM user JOIN usertype USING (usertype_id) WHERE usertype.name = 'admin' ";

3) Define constants that match the keys in the usertype table:

// As defines
define("USERTYPE_ADMIN",1);
define("USERTYPE_REGUSER",2);

//Or as class constants
const USERTYPE_ADMIN = 1;
const USERTYPE_REGUSER = 2;

And then do a simple select.

$query = "SELECT info, foo FROM user WHERE usertype_id = " . USERTYPE_ADMIN;

This is probably the most resource-efficient solution, but it is bad to maintain, as you have to update both the table and the code if you need to modify something in the usertype table..

4) Scrap the usertype table and only keep the types in the php code. I don't really like this because it lets any value get into the database and get assigned to the type of user. But maybe, all things considered, it isn't so bad and i'm just complicating something that should be simple..

Anyways, to sum it up the solution I like most is #2 because it's coherent and with an index on usertype.name, it can't be that bad. But what I've often ended up using is #3, for efficiency.

How would you do it? Any better solutions?

(edit: fixed query in #2)

Answer 1

I almost always go for option 3). You could generate the code needed automatically based on what is available in the DB. The only thing you have to remember then is that you have to run the script to update/rewrite that info when you add another role (but if you're using phing or a similar build tool to deploy your apps, just add a build rule for it to your deploy script and it will always be run whenever you deploy your code :p).

Answer 2

I would suggest #3 to avoid useless queries, and prevent risk of behavior changes if existing DB table rows are incidentally modified:

• Adding the necessary constants in the model class:

  class Role // + use namespaces if possible
  {
    // A good ORM could be able to generate it (see @wimvds answer)
    const ADMIN = 1;
    const USER = 2;
    const GUEST = 3;
  
    //...
  

  }

• Then querying like this makes sense:

  $query = "SELECT info, foo FROM user WHERE role_id = ".Role::ADMIN;
  

  With an ORM (e.g. Propel in the example below) you'll end up doing:

  $isAdminResults = UserQuery::create()->filterByRoleId(Role::ADMIN);