Selecting from database, question.

[eluser]NateL[/eluser]
In a lot of applications, things that are user submitted are often held in a moderation queue.

It's displayed publicly when the status of the content has been set to 'Approved'

If I have a table in my database named "posts" with a column called "status", and I have various different statuses - Approved, Pending Approval, Rejected, etc....and I want to pull all of the contents that are "Approved", is it better to have each status represented as an integer?

In other words, is it OK to run a query like this:

Code:

SELECT * FROM posts WHERE status = 'approved';

Or, is it better to have the status represented as a number, like so:

1 - Approved
2 - Pending Approval
3 - Rejected

Code:

SELECT * FROM posts WHERE status = 1;

Thanks

[eluser]Jondolar[/eluser]
I recommend using a status_id and using the id for everything up to the point where you are ready to display the actual text. Then you can change the text of "approved" at any time without garfing your code.

[eluser]vivar[/eluser]
Agreed. You may find it useful to create a "lookup" table that holds the various status types (ie. create a status table with columns: id (PK), name).

The PK of this 'status' table (ie. id) can be used as a FK in a table that requires a status_id (ie. the 'posts' table references the column 'id' of the 'status' table). Then, as Jondolar pointed out, you can rename a status in the future without breaking your code and if you need to display the status name (ie. 'approved') you can get the name from the status table using a simple SQL join.