January 11, 2007 at 2:34 am
Folks,
I recently begin work for a small firm and I have inherited a large legacy asp classic codebase (no documentation, no comments) and a number of databases from a former employee. There is a particular problem that I would appreciate help with.
There is a table with a table called basestation, each record of which represents a wireless transmitter and the frequency it runs on etc. Each basestation entry has a field 'City' which describes it. However in reality a real city can have more than one basestation which can operate on different frequencies, So you have Say 'Clarkstown_10ghz', 'Clarkstown_3.75ghz', 'Clarkstown_2.4ghz'.
The problem is that this guy has also used the basestation.city field name as the lookup field for 'Cities' in the Sales order forms and elsewhere. In fact, all queries, reports, order forms etc throughout the asp classic system (internet and intranet) use the basestation.city as a lookup when trying to join on city or look for city related data. Customers records use the basestation.city in both their customers.City and customers.Basestation fields.
Now, I obviously need to able to seperate the concept of 'city' from 'basestation' for god knows how many reasons. Does anyone know a good refactoring for this kind of problem?
I would really appreciate some help with it.
Thanks
Mick
January 15, 2007 at 8:00 am
This was removed by the editor as SPAM
January 16, 2007 at 6:35 am
If I were going to refactor this, I'd leave the existing column in place as a calculated column and then split the actual data storage into two other columns. My two cents.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 17, 2007 at 6:29 am
Thinking about it, that's a somewhat inadequate answer I gave.
Referring to Ambler's book on refactoring, of course you want to first add the new columns. Then you have a decision to make as to how much you want to do and where you have to do it. First off, if you have nothing but TSQL access to the data through stored procs, you can do what I suggested, creating the main column as a calculated column & then you just have to refactor the insert & update stored procs to put the data into the seperate fields. If you've got ad hoc or client side TSQL, you'll want to go the other direction & have a trigger insert & update the data into the seperated fields as the combined field gets modified.
That's a bit better. Hope it sheds more light than heat.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 10, 2007 at 4:34 am
Thanks Grant,
I shall try that out. The problem is that this database is currently MYSQL (We intend to move to MSSQL) and using triggers etc is nerve-wracking. I have amblers book. I guess I should read it more closely.
Cheers
Mick
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply