define tables and columns names with constant

Question

is it a good coding style to define all tables and columns names with a constant and then using them in a query string? i'm doing so, because whenever I change something in my database, I can simply change constant value instead of changing everything. for example here is my database definitions in a separate file:

    define("DB_TBL_USER", "user");

    define("DB_USER__PK_USER_ID", "pk_user_id");
    define("DB_USER__PASSWORD", "password");
    define("DB_USER__EMAIL", "email");
    define("DB_USER__FIRST_NAME", "first_name");
    define("DB_USER__LAST_NAME", "last_name");
    define("DB_USER__GENDER", "gender");

and in my database class i built query like this (in register_user() method!):

$queries[]="INSERT INTO ".
                            constant('DB_TBL_USER'). " ( ".
                            constant('DB_USER__PASSWORD').",".
                            constant('DB_USER__EMAIL').",".
                            constant('DB_USER__FIRST_NAME').",".
                            constant('DB_USER__LAST_NAME').",".
                            constant('DB_USER__GENDER').") ".
                        "VALUES ("."
                            $password, 
                            $email, 
                            $first_name, 
                            $last_name, 
                            $gender
                            ";

and then putting other queries in queries[] array and executing them with a loop.

is there any simpler and more efficient way to do so?

Perhaps you should worry a bit more about SQL injection attacks than making changes easy. — Ignacio Vazquez-Abrams, Aug 22 '14 at 15:33
thanks @IgnacioVazquez-Abrams but if you mean password encryption and sanitizing strings, I'm already aware of them and I implemented them. I just start learning php and mysql and I know there would be much stuffs i gonna change in my database later. — artronics, Aug 22 '14 at 15:43
may be you can try a ORM part in popular MVC frameworks(php: Codeigniter,Yii etc...), you may not need to reinvent the wheel in the context of sql query, result generation - https://ellislab.com/codeigniter/user-guide/database/active_record.html — Buddhi, Aug 22 '14 at 15:43
@buddhi. I'm reading the documentation. It'd help me. thanks — artronics, Aug 22 '14 at 16:02

score 3 · Accepted Answer · answered Aug 22 '14 at 15:45

In practice, renaming a column doesn't happen that often. Adding or removing them is much more common, and for that your constants won't help.

The main reason why a column would change, is when its meaning changes, for instance when you want to store initials instead of first name, you could rename the column first_name to initials.

But then, if you have a constant, you would still need to rename the constant too, otherwise you would have a constant name that is meaningless and confusing.

It might still be easier, since DB_USER__FIRST_NAME can be more easily found in your code than first_name (which may result in false positives for variable names and such).

Anyway: If you do this, I would use the constant with just the name DB_TBL_USER instead of constant('DB_TBL_USER'). If you do that, your code is leaner and you may benefit from code completion features in your IDE.

If you don't, and you would in a very rare case need to rename first_name, you can still just search the entire code and check every match whether it needs to be changed. Even in very big projects this is quire manageable.

Alternatively, you might consider adding the table name as a prefix to the column names as well, so name the column user_first_name instead of just first_name. Not the path I would choose, but it is done sometimes (MediaWiki uses this pattern, for instance).

If you use ORM and/or a command pattern, you might wrap access to your table in a handful of small classes, thus making it trivial to change a column name. Renaming the property that maps to the column can be done by refactoring tools if you have a proper IDE, and otherwise the same search/replace remark as above applies.

I think, I'm too much worry about that! about using DB_TABLE_USER instead of constant('DB_TBL_USER'), I think there is no way to do that according to [this](http://stackoverflow.com/questions/2203943/include-constant-in-string-without-concatenating) thanks, — artronics, Aug 22 '14 at 16:10
That question is about expanding constants inside a string without concatinating. Since you *are* concatinating, that is very possible: `INSERT INTO '.DB_TABLE_USER.' ....'` will work just fine. — GolezTrol, Aug 22 '14 at 16:58

define tables and columns names with constant

1 Answers1