How to make schema and code dynamic?

I want to make my database schema and application code as dynamic as possible to handle "unknown" use cases and changes. Developing in PHP and MySQL. Twice now I have had to change my entire schema including table and column names and this means the developers have to go back to the application code and modify all the SQL queries and table/columns names. So to prevent this I want to if just like we do on pages where we have page content, ti开发者_开发问答tle bar etc dynamic like a %variable%, can we do it for the schema and maybe even for the php code functions and classes somehow? It takes weeks to re-do all changes like this vs if it is dynamic it can be done in under a day.

---

First of all, may you have a happy new year (regardless of your coding issues :) ). Now, what I'm going to proposse to you applies to nearly any development language, but I pressume that PHP suffers this most due to the lack of OO development.

The main issue with changin schemas and functions is the definition of your architecture. To have a robust architecture, you should:

• Identify the classes that compose your application
• Create the interfaces to comunicate with these clases
• Abstract your business(domain) classes from your data classes

Consider using (if you are not using it already) an ORM framework for mapping your database, since you are using PHP, I would suggest Propel or Doctrine, which are wonderful. Beware of depending too much on your ORM framework and converting it into your business model, you can check out this blog post I made talking on the subject.

Hope I can help, David

---

What stage of development are you at? And are you talking about a redesign or just refactoring names and so on? Some thoughts:

1. If you're in the early stages of development, perhaps playing around to get a feel for how the design might shape up, then it's natural to rewrite your proof-of-concept application.

2. If you're in production, and your design really has changed (as user requirements mature - it happens in real life) then surely your old application is now out of date and has to be rewritten anyway.

3. If you're just renaming things, and not really redesigning, then do you really need to do it at all? Names of internal things like tables and columns and classes and variables don't need to be updated just because the user-visible terminology changes.

Basically I'm saying that a dynamic design such as you envisage strikes me as a poor foundation to build upon.

But that's not to say that you can't have a flexible design. Simplest case: normalising your database to about third normal form generally makes for flexibility, because you can add columns, create new relationships, and so on. Same goes for small classes with well-defined interfaces.

Assuming you want a strong design to build your system on, I recommend that you make a flexible design (hard work) and don't look for push-button solutions. (I wonder whether I've understood your question...?)

---

You could include a special PHP file at the beginning of all your scripts, and this file can contain predefined constants that you use throughout your code in place of all table and column names. Then, whenever you need to change the name of a table or column, you only need to change it in 1 place inside of this 1 PHP file.

See PHP documentation on constants here:
http://php.net/manual/en/language.constants.php

As you can see, constants exist in a global scope. That means they can be used inside of all functions and all classes without any problems.

You probably already know about the use of "include" (for loading the special PHP file at the beginning of all your scripts):
http://php.net/manual/en/function.include.php

So, for example, if you wanted to write some SQL using dynamic table and column names, you can do this:

Inside constants.php:

define("USER_TABLE",  "Users");
define("NAME_COLUMN", "FirstName");

Inside your scripts you do this:

include "constants.php";
...
$sql = "select " . NAME_COLUMN . " from " . USER_TABLE . " where id = ...(etc)";

The above translates to this:

$sql = "select FirstName from Users where id = ...(etc)";

If you someday later want to change the name of the table and column, you only need to change the constants.php file.

define("USER_TABLE",  "DifferentUsers");
define("NAME_COLUMN", "DifferentFirstName");

That will automatically cause the changes throughout all of your scripts that use this include file.

The new translated result would now look like:

$sql = "select DifferentFirstName from DifferentUsers where id = ...(etc)";