|
|
|
Grasshopper
 
Group: General Forum Members
Last Login: Thursday, April 30, 2009 4:59 PM
Points: 14,
Visits: 66
|
|
Hi,
I'm trying to replace current mainframe system which uses mainframe application with backend mainframe database with SQL database in backend and Windows application written on C#. Currently looking at mainframe database, they have different fields using the same information. For example, They have Home address of 20 chars length and then another field street number which is 4 character length and consists of same information as first 4 characters of Home address field. To me, it seems like some kind of programming constraints of mainframes. In sql we can use string functions like substring etc. to get out first 4 chars from Home address. From the way I think, 2 different columns are not necessary. What would be the pros and cons of having 2 different columns or not having 2 different columns? Which one sounds more logical? I would appreciate your help.
Thanks,
Tur 
|
|
|
|
|
SSC Journeyman
Group: General Forum Members
Last Login: Friday, July 15, 2011 3:28 AM
Points: 87,
Visits: 121
|
|
Hi,
If the smaller field is indexed it would be a lot smaller than on the full address.
If there's a postcode field then all that may be required to establish a full exact address is to combine the value from the small field with the result from a postcode lookup service / db. Whilst the larger address field is there for when a postcode lookup may not work (ie. for some business addresses).
Cheers, Jode
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 2:21 PM
Points: 11,885,
Visits: 22,843
|
|
From a strict, structural stand point, no it doesn't make any sense to break that data out in that fashion. Not unless you're breaking down all the address information so that the street is in a seperate field too.
But, there could be a business or functional reason to seperate out that data. None come to mind at the moment, but that doesn't mean there isn't one.
However, in general, no I wouldn't do that with data that I can easily pull when needed with little overhead or performance hit.
----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for Red Gate Software
|
|
|
|
|
SSCertifiable
Group: General Forum Members
Last Login: Today @ 4:40 AM
Points: 6,627,
Visits: 7,336
|
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Thursday, April 30, 2009 4:59 PM
Points: 14,
Visits: 66
|
|
Hi Jody Andrews, Grant Fritchey, ALZDBA,
Thanks for your valuable input. Jody, I agree with you that smaller columns takes less space when we create index to increase query performance if this smaller column has some valuable data or uniqueness. Home Address was just an example cooked up by me. But still I think, this extra column will add space overhead to our sql database. Grant, I agree with you that if some information can be gathered easily from already existing column, its stupidity and against normalization rules of sql to add new column with substring of another field. Thanks ALZDBA for taking me one step further towards the migration process. I haven't reached that point yet. We will have some mainframe programmers write the program to extract data to the flat file and SQL developers will use that flat file to import to the SQL database yet to be designed.
I wanted to know the pros and cons like space usage, performance etc. between our option to keep the same field or different field.
Please feel free to add anything you think might be helpful for me or anything you have gone through before.
Thanks,
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 2,425,
Visits: 2,322
|
|
To me, it seems like some kind of programming constraints of mainframes
The strange thing you see is due to the application design, not to the platform it is running on. Speaking as a former mainframe person you may not believe this, but I have also seen some wierd designs on Windows systems...
Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2005, 2008, 2008 R2 and 2012. 16 December 2011: now over 17,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
|
|
|
|
|
Mr or Mrs. 500
Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 516,
Visits: 1,558
|
|
The main reason for breaking something like address into seperate fields would be validation, things like street names, street types, etc. can be in lookup tables. I see no reason to have the fields seperated but then have a field that is a combination of more than one field. You could check to see if there is a problem with the extracting of the data that picks up the same data more than once.
Steve
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: Thursday, November 17, 2011 4:09 PM
Points: 9,359,
Visits: 8,864
|
|
Actually, I have seen perfectly valid reasons for having both the extracted subfields and the whole address as originally input.
The crux of it is that addresses are notoriously dirty, mangy and amorphous pieces of text, that can virtually never be parsed by a computer with 100% accuracy. Consequently, some apps and business that I know of attempt to parse out the address into more codified subfields (like street number, etc.), but these are never truly complete. Therefore, it is important to keep the original address text, potentially for prodcing the label, but also for human inspection to determine of the subfiled extraction was correct and because if improved algorithims are brought in later, you may want it to repars some of the older, mangier addressses.
And finally, there may be legal issues and reasons why the exact original text must be preserved.
-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."
|
|
|
|
