March 27, 2009 at 9:30 am
I have a project where I'm taking a mainframe file and pulling it into SQL Server and I have a bit of a quandary: To normalize, or not to normalize.
The data is a Cobol multi-screen app where there are different record types and some screens are applicable to specific record types where other screens are not. Unnormalized, the records are huge: over 500 fields, a bit under 4K in total record length if memory serves, and there's 130,000 or so of 'em. Normalized, I split it into 4 tables with radically smaller record counts and sizes.
Here's my issue: this is a very limited life application. Next to zero insert activity, light update activity, and that will decline over the next year or two to absolute zero as all new activity is going into our ERP system, this is data that cannot be migrated to that platform.
I'm considering not normalizing it because my VB Fu is weak and the thought of managing multiple database connections between screens seems unpleasant to me. It's sort of a work order/project system, and once the last project is closed, it will become a read-only system, so ultimately it will be a query-only/print system.
I'm thinking not normalizing is the way to go because it would guarantee preserving the original organizational structure and logic. It goes against the grain of almost all we stand for when it comes to database design, but I think this might be one of those exceptions where not normalizing would be an acceptable thing to do in this one case.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
March 27, 2009 at 9:33 am
Given those circumstances, I'd probably not bother normalizing.
I'm not sure what VB has to do with that, but regardless of that, the extra work doesn't look like it would have any measurable payback.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 27, 2009 at 9:49 am
I have to write the front end application for it, that's the only thing that VB applies to. It would probably make no difference what language I did it in, but VB is about the only programming language that I know any more outside of T-SQL and Perl.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
March 27, 2009 at 10:02 am
Makes sense.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 21, 2009 at 12:39 am
From experience i have learnt that many applications that are billed as limited use and short lifespan, become semi permanent and even permanent solutions. The quick and dirty approach could bit you if this is a possibility. You could try something down the middle. Assuming that the mainframe recordset is in a flat text file, you could just put the original text file as is in an accessible place on the SQL box and use the openrowset function with a format file to expose it as a table in SQL and by inference to VB. You could then add a few tables to store the changes connected to some unique record identifier in the original data. (If you only have a few fields to take into account).
Stephen Marais
Integration Architect
Digiata Technologies
www.digiata.com
April 22, 2009 at 2:11 am
We generally use DTS to place the raw data in a SQL table, then use a stored procedure to covnert the flat file data into relational data. The original feed then gets moved to an archive table. We use T-SQL to do all of the owrk, and it's fast and reliable.
April 30, 2009 at 11:44 am
I've been thinking about this project, it looks like I might actually get to do some work on it soon. I think I'm going to do a "logical split", for want of a better term. I'm going to try keeping all of the data in an unnormalized fashion, then have views breaking each section apart. It will make it much easier to work with when it comes to developing screens as I'll have the specific information that I need to deal with rather than the X-hundred additional columns that are not needed.
This is definitely a finite-life system: the users are using other systems for current work, so this one will dwindle in work as current projects are closed out over the next few years until it's entirely dead and we'll be able to archive it.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply