April 29, 2009 at 9:49 pm
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 😀
April 30, 2009 at 5:07 am
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
April 30, 2009 at 7:22 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 30, 2009 at 7:41 am
also keep in mind when migrating an existing data system, you will have to deal with legacy short comings, data or data structures that doesn't make sense, but is those errors may even not come to surface in the current applications because of the way things are written, data and data usage dependencies,...
With sqlserver you have "calculated columns" which may give you the same result as what you are seeing in your current systems layout.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 30, 2009 at 4:55 pm
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,
😀
May 5, 2009 at 3:01 am
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...
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 12, 2009 at 1:25 pm
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
May 12, 2009 at 4:23 pm
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply