Singluar data-keys between application and database?

Question

Is there a paradigm in which I can change a data-key name in one place and one place only, and have it properly be dealt with by both the application and database?

I have resorted most recently to using class constants to map to database field names, but I still have to keep those aligned with the raw database keys.

What I mean is, using PHP as an example, right now I might use

$infoToUpdateUser[ User::FIELD_FIRST_NAME ]

This means that when I change it at the constant, I don't have to search through the code to change all references to that field.

Another area this crops up in is in referencing fields. Due to some early poor design decisions, I have, for example, these sorts of tables:

( table name : primary_key )

• cats : cat_id
• dogs : dog_id
• parrots : bird_id (remember, poor design, thus the mismatch between parrots / bird_id)
• lizards: lizard_id
• etc

Then let's say I have a series of form classes that update records.

• AnimalForm
• DogForm extends AnimalForm
• CatForm extends AnimalForm
• ParrotForm extends AnimalForm
• etc

Now I want to update a record in the SQL database using an update function in the parent class, AnimalForm, so I don't have to replicate code in 20 subclasses.

However I do not know of a way to generalize the update query, so currently each subclass has an idFieldName member variable, and the parent class inserts that into the query, like

"UPDATE " . $this->table . " SET <data> WHERE " . $this->idFieldName

It seems sloppy to do it this way but I can't think of a better solution at this point.

Is there a design model or paradigm that links together or abstracts data-key names to be shared as a reference by both a database and an application?

Answer 1

What you are looking for is called an Object-Relational Mapping layer.

An ORM separates the concerns of data access from business logic by mapping a relational database into an object model. Since the ORM does all the translation, if you change the name of a database table or column, you only have to tell the ORM once, and it will properly apply that change to all of your code.

Since you indicate that you are using PHP, here is a question that addresses ORM libraries in PHP. Additional information about ORM technologies can be found in Wikipedia.