But we wanna do it this way



  • We have about 50-ish DB tables, most of which have 300-800 million rows. About 10 have > 1 billion rows. Five have > 3 billion rows. We are adding about 4TB/month. Several new aspects of our business promise to increase the rate at which we generate data that must be stored. Naturally, our users are concerned about the never ending increase in loaded storage costs.

    The SAs, DBAs and my boss have finally bombarded our (extremely well educated, yet computer illiterate) users with enough demands that they HAVE to let us partition the tables so that the data can be aged and periodically moved to cheaper offline storage (within in a reasonable time frame) beyond a certain point.

    We presented two options:

    1. Modify the existing tables to have an unused "partitionId" column; modify the application to start generating partition id's for all new data, and insert data into both sets of tables (partitioned and unpartitioned). In the background, generate partition id's for all existing data. When that's done, copy the unpartitioned data with partition id key into the partitioned tables. Finally, drop the old tables to reclaim space. Time required: several people for one year to write all the necessary scripts, run them on modest enough chunks of data so as to keep the transaction size mangeable, and to not bury the network, incinerate the logs, and so forth.

    2. Create new (empty) partitioned tables. Change the code to insert only into the partitioned tables. Create a view to let the users look up data that's in either table. Time required: 2-3 months to write and test scripts, change existing stored procs to use the new table names, and verify there are no ripple effects into other systems.

    Yes, there are other variants, but these were the options that minimized special case handling.

    Method 1 carries considerable cost: duplicating all the affected tables for a year. It carries considerable risk (our data is not well sanitized, so an orhpaned row could trash a large update, with massive rollbacks, etc). It will take a very long time to do all the grinding.

    Method 2 carries virtually no cost (no additional storage will be required. The monthly allocation will simply go for the new tables instead of the old ones). There is virtually no risk, as any bad data in the old tables will continue to sit there, unaffected. Time to actually implement it is reduced to about an hour to run the rename scripts and install a new version of our software. All the DBAs, SAs, developers and development manageers agree method  2 is the way to go.

    Our users decide they want method 1. Why? After a lot of back and forth; because we want to!

    We're all banging our heads against our desks.

     



  • Why give them a choice involving things that won't affect their user experience? Why include the customer in implementation decisions at all?



  • Your users went with the solution that matches the scale of the precieved problem.



  • Yeah, TRWTF here is you guys, for offering choices to the client that they are in no way technically qualified to evaluate.

    Hell, just go with option 2 anyway. It's not like they'd ever be able to tell the difference.



  • @lettucemode said:

    Why give them a choice
    Because we have to sell them on the fact that it's a project that needs doing. Around here, only they make priority decisions on what gets done and (sadly) how. It's just the way it is, and it's backed by c-level authority. Since they asked for options (plural), we came up with several. We narrowed it down to a simple choice. We presented a horrible choice and an easy choice. We created power point slides with faces-in-pain and smiling faces. We used forboding dark red and blue for option 1 and bright orange and green  to highlight option 2. We did everything short of poking them with pointy sticks.

    I guess it's true; no amount of intelligence can compensate for sheer stupidity.

     



  • Next time remember to do this:
    Option 1. Best option
    Option 2. Do nothing and suffer



  • The problem is that you are thinking like a rational person who knows what the choices mean, and you expect that the people making these decisions are the same.

    You need to see these things the way they do:

     "Those are some interesting choices.  Let me think about that for a moment."  (Well, the first choice seems awfully expensive and  needlessly cautious.  Why are these tech people presenting it to me when the second option is so obviously better?)

     (Oh, I get it.  They're trying to trick me into doing something dangerous so that when it all goes bad they can blame me for not choosing the first option!  I have no clue whatsoever what could go wrong, and I am convinced that everyone around me is a lying bastard who only wants to betray me, sleep with my dog, shoot my wife, ruin my career, steal my stapler and then drink wine from a cup made out of my skull, so asking questions which might betray my lack of understanding is right out.  Logically, the only safe choice is to go with option one.)

     "Let's go with the first one.  That looks good to me."

     "Why do I want that?"  (Don't make any sudden moves... stand in front of the stapler so he can't see it... hold onto your head just in case he makes a grab for it...  Is that a hint of merlot that I smell?)  "Um, no reason.  We just want to!"

     



  • @snoofle said:

    We presented two options [...]. Our users decide they want method 1. [...] We're all banging our heads against our desks.

    Today's lesson: when you present two options to non-technical people, you should make sure that none of these options would lead you to bang your head on your desk. Or at least don't put numbers in the options so you can fudge your way to a "misunderstanding" once the right solution has been implemented.



  • @snoofle said:

    We presented a horrible choice and an easy choice. We created power point slides with faces-in-pain and smiling faces. We used forboding dark red and blue for option 1 and bright orange and green  to highlight option 2. We did everything short of poking them with pointy sticks.

    You neglected to assign code names. Option 1, "Operation RECTAL WARTS", and Option 2, "Operation BUNNY HUGS".

     

     



  • Ah, Snoofle is working for Telmex...



  • @snoofle said:

    @lettucemode said:
    Why give them a choice
    Because we have to sell them on the fact that it's a project that needs doing. Around here, only they make priority decisions on what gets done and (sadly) how. It's just the way it is, and it's backed by c-level authority. Since they asked for options (plural), we came up with several.
    The mistake is giving them multiple options. As nicely explained here:@DCRoss said:
    (Oh, I get it.  They're trying to trick me into doing something dangerous so that when it all goes bad they can blame me for not choosing the first option!  I have no clue whatsoever what could go wrong, and I am convinced that everyone around me is a lying bastard who only wants to betray me, sleep with my dog, shoot my wife, ruin my career, steal my stapler and then drink wine from a cup made out of my skull, so asking questions which might betray my lack of understanding is right out.  Logically, the only safe choice is to go with option one.)

     "Let's go with the first one.  That looks good to me."

    Yes, I know they asked for options (plural) so in that case you should have said to them:  "We've carefully examined all the options, and they are all very difficult, expensive and dangerous, except for this one, it's relatively fast, easy and safe."  And then present the one good option to them.

     



  • @snoofle said:

    We have about 50-ish DB tables, most of which have 300-800 million rows. About 10 have > 1 billion rows. Five have > 3 billion rows. We are adding about 4TB/month. Several new aspects of our business promise to increase the rate at which we generate data that must be stored. Naturally, our users are concerned about the never ending increase in loaded storage costs.

    The SAs, DBAs and my boss have finally bombarded our (extremely well educated, yet computer illiterate) users with enough demands that they HAVE to let us partition the tables so that the data can be aged and periodically moved to cheaper offline storage (within in a reasonable time frame) beyond a certain point.

    We presented two options:

    1. Modify the existing tables to have an unused "partitionId" column; modify the application to start generating partition id's for all new data, and insert data into both sets of tables (partitioned and unpartitioned). In the background, generate partition id's for all existing data. When that's done, copy the unpartitioned data with partition id key into the partitioned tables. Finally, drop the old tables to reclaim space. Time required: several people for one year to write all the necessary scripts, run them on modest enough chunks of data so as to keep the transaction size mangeable, and to not bury the network, incinerate the logs, and so forth.

    2. Create new (empty) partitioned tables. Change the code to insert only into the partitioned tables. Create a view to let the users look up data that's in either table. Time required: 2-3 months to write and test scripts, change existing stored procs to use the new table names, and verify there are no ripple effects into other systems.

    Yes, there are other variants, but these were the options that minimized special case handling.

    Method 1 carries considerable cost: duplicating all the affected tables for a year. It carries considerable risk (our data is not well sanitized, so an orhpaned row could trash a large update, with massive rollbacks, etc). It will take a very long time to do all the grinding.

    Method 2 carries virtually no cost (no additional storage will be required. The monthly allocation will simply go for the new tables instead of the old ones). There is virtually no risk, as any bad data in the old tables will continue to sit there, unaffected. Time to actually implement it is reduced to about an hour to run the rename scripts and install a new version of our software. All the DBAs, SAs, developers and development manageers agree method  2 is the way to go.

    Our users decide they want method 1. Why? After a lot of back and forth; because we want to!

    We're all banging our heads against our desks.



    Wow, that sucks.

    Just curious; what DB are you using?



  • @Speakerphone Dude said:

    @snoofle said:

    We presented two options [...]. Our users decide they want method 1. [...] We're all banging our heads against our desks.

    Today's lesson: when you present two options to non-technical people, you should make sure that none of these options would lead you to bang your head on your desk. Or at least don't put numbers in the options so you can fudge your way to a "misunderstanding" once the right solution has been implemented.

    Yeah, that particular method bit some of our teachers (and, as a result of this method, their pupils) directly in the ass.

    Some years back, finishing the Gymnasium (German high school) meant doing the Abitur by having the teacher present two versions of the final exam for his teaching subject to a committee. Two versions with different questions / experiments / texts / ...

    Some teachers did not want to prepare their pupils for all the topics covered by both versions, so they gave one good and one horrible version of the exam to the committee. Expecting them to choose the good one.

    Worked most of the time...

    (In case you were wondering: It's working differently now)



  • 1. Tell users they're getting Option 1, and it will take a year.

    2. Do Option 2 in two months.

    3. Take a ten month vacation.

     



  • @ShatteredArm said:

    1. Tell users they're getting Option 1, and it will take a year.

    2. Do Option 2 in two months.

    3. Take a ten month vacation.

     

    You could even take a 2nd job where you would also not work... some people do it for a long time before getting caught.



  • @ShatteredArm said:

    1. Tell users they're getting Option 1, and it will take a year.

    2. Do Option 2 in two months.

    3. Take a ten month vacation.

     

     

    That's exactly what I was going to suggest...

     



  • @Speakerphone Dude said:

    @ShatteredArm said:

    1. Tell users they're getting Option 1, and it will take a year.

    2. Do Option 2 in two months.

    3. Take a ten month vacation.

     

    You could even take a 2nd job where you would also not work... some people do it for a long time before getting caught.

    That guy is my new hero.

    So long, Guy Who Invented Beer!



  • @Speakerphone Dude said:

    @ShatteredArm said:

    1. Tell users they're getting Option 1, and it will take a year.

    2. Do Option 2 in two months.

    3. Take a ten month vacation.

    You could even take a 2nd job where you would also not work... some people do it for a long time before getting caught.

     

    @The article said:

    The Justice Department says Aerospace, which received $900 million in
    taxpayer money this year, was aware of Hunter's regular absences but
    still billed for his services -- and at a rate higher than what he was
    paid, Assistant U.S. Attorney Howard Daniels told the paper.

    Lots of damning stuff in here...and then this. They billed for his work at a higher rate than his salary. Isn't that pretty standard?

    I don't remember the exact rate, but IIRC when a client needs custom feature work done and it lands on my desk, they generally get charged somewhere in the neighborhood of $200/hr for the time I spend coding it.  I make a pretty decent living at this job, but not that good!

     



  • @Mason Wheeler said:

    Lots of damning stuff in here...and then this. They billed for his work at a higher rate than his salary. Isn't that pretty standard?

    It is. I did a double-take myself, but then assumed it was just the writer's clumsy way of saying "This cost taxpayers a lot more than $137,000 /year."



  • @snoofle said:

    Because we have to sell them on the fact that it's a project that needs doing. Around here, only they make priority decisions on what gets done and (sadly) how. It's just the way it is, and it's backed by c-level authority. Since they asked for options (plural), we came up with several. We narrowed it down to a simple choice.

     


    Never present an option you're not OK with implementing.  I've spoken to C-level executives of many very large organizations, and I would never present one of them with an option I considered terrible.  I'd probably lead off with "there are no other viable solutions", but if they absolutely had to have a choice, I'd pick two variants of the same basic plan.


  • @Cat said:

    I've spoken to C-level executives of many very large organizations, and I would never present one of them with an option I considered terrible.  I'd probably lead off with "there are no other viable solutions", but if they absolutely had to have a choice, I'd pick two variants of the same basic plan.

    I don't know what you mean by "very large organizations" but C-level executives in Fortune 500 usually don't have time (or interest) to hear about technical options; they rely on the guy 2-3 layers under the CIO/CTO to review alternatives and come up with *one* plan that is summarized on a one-pager (ideally in bullet points). High level executives are defining the business strategy and the 10,000 feet budget allocation, they don't pick between Exchange or Domino. It's the Enterprise Architect (or equivalent)'s job to make sure that the technical decisions are aligned with the business strategy defined by the big cheese.

    Of course in smaller companies some dude may call himself the CEO and his brother-in-law can be the Enterprise Architect; in that context, yes, those people may have to make a technical decision (such as choosing between a shared account on Salesforce or a bootleg copy of Maximizer) and in this case it is a good idea to go with the "no other viable solution" approach.



  • Option 3: Do Option 2, but have your DBAs create views with PartitionIDs in them that you can use to present your users with evidence of solid and steady progress in pursuit of Option 1.



  • Your mistake was in offering them two options. You should have offered them three. Ranked in terms of cost and/or time and/or stress, numbered, one, two, or three, people will almost always choose option two.


  • :belt_onion:

    @AndyCanfield said:

    Your mistake was in offering them two options. You should have offered them three. Ranked in terms of cost and/or time and/or stress, numbered, one, two, or three, people will almost always choose option two.

    Funny but every time I present a client with multiple options they just tend to choose the cheapest. Of course they usually don't have any vision on the future so they never pick the slightly more expensive option that saves them money in the long run.

     



  • @bjolling said:

    @AndyCanfield said:

    Your mistake was in offering them two options. You should have offered them three. Ranked in terms of cost and/or time and/or stress, numbered, one, two, or three, people will almost always choose option two.

    Funny but every time I present a client with multiple options they just tend to choose the cheapest. Of course they usually don't have any vision on the future so they never pick the slightly more expensive option that saves them money in the long run.

     

    One time my employer wanted to buy 125 PC. Our primary vendor had a good deal for decent machines for about 350$00 a piece but management wanted an even cheaper model listed on their website (275$00). The vendor was not thrilled and told us that those were total POS (and seeing the BOM I agreed - the flimsy keyboard and the mouse with no scroll wheel were already a major red flag), he even lowered his price to 300$00 for the "good units". Management decided to go with the 275$00 against our advice even if the vendor warned us that on those machines we would have to process the RMAs ourselves. Within a year more than half of those units were broken. (I also suspect that if there was a medical follow-up the people using those PC would show an increased cancer rate). But hey, we "saved" a bundle.



  • I'd want to do some impact testing of the switch from a table-based query to a view. I assume the view will be a UNION query, and there could be major impacts on performance (especially with indexed columns). I've had a similar issue here on a much smaller scale.

    I suspect that your users may have unwittingly taken the right option, for the wrong reasons!



  • With enough handwaving, obfuscation, operational minutae, and selective highlighting of alleged special cases (in short, bullshit), a single option could be presented three times over to give a computer-illiterate customer the choice of which of the three to go with. Be sure that at least one option includes a heavy human component that could in reality be performed by a very small shell script - for that ten-month vacation.



  • @El_Heffe said:

    @snoofle said:

    @lettucemode said:
    Why give them a choice
    Because we have to sell them on the fact that it's a project that needs doing. Around here, only they make priority decisions on what gets done and (sadly) how. It's just the way it is, and it's backed by c-level authority. Since they asked for options (plural), we came up with several.
    The mistake is giving them multiple options. As nicely explained here:@DCRoss said:
    (Oh, I get it. 
    They're trying to trick me into doing something dangerous so that when
    it all goes bad they can blame me for not choosing the first option!  I
    have no clue whatsoever what could go wrong, and I am convinced that
    everyone around me is a lying bastard who only wants to betray me, sleep
    with my dog, shoot my wife, ruin my career, steal my stapler and then
    drink wine from a cup made out of my skull, so asking questions which
    might betray my lack of understanding is right out.  Logically, the only
    safe choice is to go with option one.)

     "Let's go with the first one.  That looks good to me."

    Yes, I know they asked for options (plural) so in that case you should have said to them:  "We've carefully examined all the options, and they are all very difficult, expensive and dangerous, except for this one, it's relatively fast, easy and safe."  And then present the one good option to them.

     

    This is what I do. When say "give us options", what they actually mean is "give us a solution so we don't have to decide" (people hate making decisions). So when someone asks you for options, don't give them every one available in the book; give them the most feasible one. If you really must give multiple, sorted them from most to least practical, because when faced with options they aren't qualified to evaluate, users will always choose the default/first one.

    snoofle, in your case, you could easily have said to your users "there is only one option, if you have any others to suggest please do so". Since they aren't technical, they won't suggest anything, and you get to do what you need to do. Is lying to users ethical? No, but does it really matter if you give them the simplest and best solution in the shortest time possible?


  • ♿ (Parody)

    @morbiuswilters said:

    @Mason Wheeler said:
    Lots of damning stuff in here...and then this. They billed for his work at a higher rate than his salary. Isn't that pretty standard?

    It is. I did a double-take myself, but then assumed it was just the writer's clumsy way of saying "This cost taxpayers a lot more than $137,000 /year."

    Mostly, I assume that the reporter is an idiot. Firstly, you have other fringe benefits (health insurance, vacation, etc). Then, the company will probably charge some for overhead (e.g., the cost of providing his office, etc), plus some profit. Every single contractor that expects to be a going concern will do this, whether the customer is the government or not. I think the urge to sensationalize got the better of Michael Winter, crack USA Today reporter.



  • @this_code_sucks said:

    Just curious; what DB are you using?
    Oracle, but Oracle isn't the problem; bad table design, inexperienced developers not taking into account the concept of aging data, and DBAs who don't really want to be bothered all contributed to this mess.



  • @Quango said:

    impact testing
    In this case, business requirements make it an either-or situation, so the view would be implemented as (pseudo): if date-of-interest < cutover-date then query old tables else query new tables, so the "union" wouldn't really be a union. Other than the overhead of doing the date comparison, there would be no performance issue.



  • @All: re: do simple version and take 10 month vacation: if it only affected our system, that would be very plausible around here, but these tables are used by other teams also; their systems would also need to change. If we did it differently than the advertised way, one of these blabbermouths would surely say the wrong thing to the wrong person, and that leads to headaches nobody wants.




  • @snoofle said:

    We presented two options:

    1.  Add partitionIDs to new data. Gradually modify old data to add partitionIDs.

    2. Create new (empty) partitioned tables. Ignore old data.

    Sorry to re-rail thread from Fraud-Waste-Abuse subject, but Option 2 doesn't seem to get around to adding partitionIDs to the old data - which seemed to be the actual goal??



  • Yep, makes sense - a union in code, rather than SQL.



  • @snoofle said:

    @this_code_sucks said:
    Just curious; what DB are you using?
    Oracle, but Oracle isn't the problem; bad table design, inexperienced developers not taking into account the concept of aging data, and DBAs who don't really want to be bothered all contributed to this mess.
     

    I though Oracle has native partioned tables?  So you could setup tables to create a partition for every year by making the key field a date and then the database would do the partitioning automatically for you.  You might even be able to move the partitions containing older data to lower cost storage.  But you wouldn't have to code anything, just figure out how to configure Oracle.  In any case, I'd look at the capabilities of Oracle before deciding on option 1 or 2.



  • @Speakerphone Dude said:

    ...about 350$00 a piece ... their website (275$00). The vendor ... price to 300$00 for ... the 275$00
     

    WTF mud pit country are you from where the dollar sign is used as a decimal separator?

     



  • @lpope187 said:

    @snoofle said:

    @this_code_sucks said:
    Just curious; what DB are you using?
    Oracle, but Oracle isn't the problem; bad table design, inexperienced developers not taking into account the concept of aging data, and DBAs who don't really want to be bothered all contributed to this mess.
     

    I though Oracle has native partioned tables?  So you could setup tables to create a partition for every year by making the key field a date and then the database would do the partitioning automatically for you.  You might even be able to move the partitions containing older data to lower cost storage.  But you wouldn't have to code anything, just figure out how to configure Oracle.  In any case, I'd look at the capabilities of Oracle before deciding on option 1 or 2.

    This is correct. In Oracle it is possible to create partitions that are using different volumes (and possibly different storage tiers), and this does not in fact impact in any way the data model. You can even configure a bunch of tables to follow a same partitioning method.

    Even better, in recent versions there is a feature (ASM) that can manage multiple volumes to perform this kind of smart storage allocation automatically.

    In any event, there are enough technologies out there to make storage-level decision and change nothing in the data model or in the process.



  • @Zylon said:

    WTF mud pit country are you from

    This comment made you lose all credibility



  • @snoofle said:

    Oracle isn't the problem

    In my experience, Oracle is always the problem

    @Zylon said:

    WTF mud pit country are you from where the dollar sign is used as a decimal separator?

    Cape Verde, but I think he is doing it to spite people



  • @Zylon said:

    @Speakerphone Dude said:

    ...about 350$00 a piece ... their website (275$00). The vendor ... price to 300$00 for ... the 275$00
     

    WTF mud pit country are you from where the dollar sign is used as a decimal separator?

    I think he's American, he just likes looking like an idiot.



  • @morbiuswilters said:

    I think he's American, because he just likes looking like an idiot.

    FTFY



  • @morbiuswilters said:

    @Zylon said:

    @Speakerphone Dude said:

    ...about 350$00 a piece ... their website (275$00). The vendor ... price to 300$00 for ... the 275$00
     

    WTF mud pit country are you from where the dollar sign is used as a decimal separator?

    I think he's American, he just likes looking like an idiot.

    Hey this is very insulting... I'm not American! But there is no point in fighting Uncle Sam's cultural imperialism so for now on I will use the inferior currency syntax (like $1.99). Yes we can!



  • @Speakerphone Dude said:

    Hey this is very insulting... I'm not American!

    You're right, it was insulting. To us. I should have known you came from the land of SpectateSwamp.



  • @shoeboy said:

    @Zylon said:

    WTF mud pit country are you from

    This comment made you lose all credibility

     

    "Credibility" is for people who deal in mushy opinion, not manly rock-hard fact.

     

     


  • Discourse touched me in a no-no place

    @Speakerphone Dude said:

    But there is no point in fighting Uncle Sam's cultural imperialism so for now on I will use the inferior currency syntax (like $1.99). Yes we can!
    You misspelt $1,99. HTH, HAND etc.


  • ♿ (Parody)

    .@PJH said:

    @Speakerphone Dude said:
    But there is no point in fighting Uncle Sam's cultural imperialism so for now on I will use the inferior currency syntax (like $1.99). Yes we can!

    You misspelt $1,99. HTH, HAND etc.

    I think you have a typo there after the comma that you have separating the $1 and the 99. And I suppose you're also missing something giving the cents unit for the 99 cents, though I'm not sure why anyone would want to write stuff like that. It just seems silly. It's OK to express dollars and cents as a single decimal number.



  • @boomzilla said:

    .@PJH said:
    @Speakerphone Dude said:
    But there is no point in fighting Uncle Sam's cultural imperialism so for now on I will use the inferior currency syntax (like $1.99). Yes we can!

    You misspelt $1,99. HTH, HAND etc.

    I think you have a typo there after the comma that you have separating the $1 and the 99. And I suppose you're also missing something giving the cents unit for the 99 cents, though I'm not sure why anyone would want to write stuff like that. It just seems silly. It's OK to express dollars and cents as a single decimal number.

    I'm getting all confused.

    • $1,c99
    • $1.99
    • $1 and 99 cents
    • 1$99
    • 1.99$
    • 1,99$
    Maybe we should always round up or use cents only. $2 or c199. That makes the American debt look even more impressive: c1569299641817846.


  • @snoofle said:

    We did everything short of poking them with pointy sticks.

    Well, now you know what you need to do next time. ☺



  • @Speakerphone Dude said:

    Maybe we should always [...] use cents only.
    Well, it works for Japan.



  • @this_code_sucks said:

    @snoofle said:

    We have about 50-ish DB tables, most of which have 300-800 million rows. About 10 have > 1 billion rows. Five have > 3 billion rows. We are adding about 4TB/month. Several new aspects of our business promise to increase the rate at which we generate data that must be stored. Naturally, our users are concerned about the never ending increase in loaded storage costs.

    The SAs, DBAs and my boss have finally bombarded our (extremely well educated, yet computer illiterate) users with enough demands that they HAVE to let us partition the tables so that the data can be aged and periodically moved to cheaper offline storage (within in a reasonable time frame) beyond a certain point.

    We presented two options:

    1. Modify the existing tables to have an unused "partitionId" column; modify the application to start generating partition id's for all new data, and insert data into both sets of tables (partitioned and unpartitioned). In the background, generate partition id's for all existing data. When that's done, copy the unpartitioned data with partition id key into the partitioned tables. Finally, drop the old tables to reclaim space. Time required: several people for one year to write all the necessary scripts, run them on modest enough chunks of data so as to keep the transaction size mangeable, and to not bury the network, incinerate the logs, and so forth.

    2. Create new (empty) partitioned tables. Change the code to insert only into the partitioned tables. Create a view to let the users look up data that's in either table. Time required: 2-3 months to write and test scripts, change existing stored procs to use the new table names, and verify there are no ripple effects into other systems.

    Yes, there are other variants, but these were the options that minimized special case handling.

    Method 1 carries considerable cost: duplicating all the affected tables for a year. It carries considerable risk (our data is not well sanitized, so an orhpaned row could trash a large update, with massive rollbacks, etc). It will take a very long time to do all the grinding.

    Method 2 carries virtually no cost (no additional storage will be required. The monthly allocation will simply go for the new tables instead of the old ones). There is virtually no risk, as any bad data in the old tables will continue to sit there, unaffected. Time to actually implement it is reduced to about an hour to run the rename scripts and install a new version of our software. All the DBAs, SAs, developers and development manageers agree method  2 is the way to go.

    Our users decide they want method 1. Why? After a lot of back and forth; because we want to!

    We're all banging our heads against our desks.



    Wow, that sucks.

    Just curious; what DB are you using?

    Access


Log in to reply