Database Mapping?

[cmgmyr]

I had a project a little while ago that I had to re-create a front end and back end of a site while keeping the same type of database structure/column names. After I got done my client came to me and said that they wanted certain column names changed after I got done with the work. They pretty much wanted:

 

dealers

=======

dname

daddress

demail

ect...

 

turned into:

dealers

=======

name

address

email

ect...

 

So I had to do back through and change all of those references in a bunch of pages and then re-test everything. So I was wondering if there was a way (easily and efficiently) to make some sort of database map so that I could just change these values in one spot instead of many spots. I was thinking of putting this in some sort of array? So I would have:

<?php
$dealers = array('dname', 'daddress', 'demail');
$sql = "INSERT INTO dealers (".$dealers[0].",".$dealers[1].",".$dealers[2].") VALUES ('$name','$address','$email')";
//OR
$dealers = array('name' => 'dname', 
'address' => 'daddress', 
'email' => 'demail');
$sql = "INSERT INTO dealers (".$dealers['name'].",".$dealers['address'].",".$dealers['email'].") VALUES ('$name','$address','$email')";
?>

 

so then I could change that array to:

<?php
$dealers = array('name' => 'name', 
'address' => 'address', 
'email' => 'email');
?>

in about 3 keystrokes.

 

I hope this makes sense how I explained it. Any ideas? Suggestions? Problems with this? Anything else?

 

Thanks!

-Chris

[roopurt18]

IMO there isn't really much that you can do that isn't a pain in the ass in situations like this and I don't really think you save much time no matter how you go about it.

 

You create a lot of work for yourself upfront.  You have to constantly manage the mappings and it becomes difficult to remember which mappings go to which columns.  For example, if you go with numeric indexes and are selecting and joining from several different tables it will be a mess to sort through.  If you go with associative indexes eventually you'll have many associations named similarly that mean totally different things.

 

The only effort this saves you is when the database changes are related to naming.  From my experience, I tend to have to rewrite queries not because of table or column name changes, but because of added constraints, requirements, or extra columns.  This means I'd have to go through and edit a bunch of existing queries anyways and the mappings would be primarily useless.

 

The biggest time saver for me in dealing with this sort of stuff is a small shell script I wrote.  Basically I just invoke the script with a search term and it very quickly searches through all of my source and finds every occurrence of the term for me.  It then creates a text file listing of:

file:line_no{tab}source

 

Then I just view it in firefox and use the built in find & highlight all function to narrow it down or I copy and paste it into edit plus to manipulate it further and remove some lines.  I know that's not much of a time-saver in this situation, but it's helped me clean up a ton of deprecated code.

[cmgmyr]

hey roop, thanks for the input. I'll have to put a little more thought into it. The master plan would be to make some other system so that I wouldn't have to change the associations that much.

 

any other ideas?

[dbo]

I mean you could potentially implement your idea cmgmyr, but I'd save the table/associative array in some sort of a global context so that you only had to modify it in 1 location for an entire project. This could potentially save you a lot of time, though I do agree with roopurt, it's typically the schema that changes not the names.