It's a Safe Thing to Do



  • Our application was written long ago. The schema was designed to store generated output data. Over time, functionality was added to calculate and store customer-specific data that would accumulate over time. This data would never expire as it was to be part of a customer "history".

    Over time, lots of other graft-on features were built. These utilized a variety of new tables that either went into the output data or customer data sections of the schema. As the number of tables grew, so did their size and the number of interrelated FKs.

    One day, the powers that be decided that the database should be partitioned, so as to be able to age out the output data. While this effort was in progress, someone decided that the partitioning should also include the customer specific data.

    Wait a minute, the customer data needs to be kept forever. The "date" on the record is irrelevant other than for sequencing, and you can use the ever-growing PK id for that more efficiently. This data would need to be partitioned by customer id range.

    The powers that be decreed that two different partitioning schemes should be used, but that the same value should be the PK across both sets of tables.
    Um, no, you can't do that; all the tables are cross linked via FKs. If you drop one set of data by date, all the records that are partitioned by customer id range will violate the FK constraints in that they'd still be referencing FKs that would no longer be there.

    The powers that be decreed that the solution was simple: drop the FK constraints! If the code is written correctly, you shouldn't need them anyway. Since the code has long since stabilized, it should be a safe thing to do.

    *headdesk*



  •  I just hope that your company outsources building maintenance...

     

    "We need some extra office space so we'll just take out this wall and expand into the next room."

    "You can't remove that!  It's a load bearing wall!"

    "Don't worry, the building has been standing for years already.  It will be fine."

     



  • @snoofle said:

    drop the FK constraints! If the code is written correctly
     

    STOP!

    There's all the justification you need to keep FKs.



  • Good analogy, DCRoss. Walls are partitions, right?



  • At least you have FKs. Here, we don't use them because everyone thinks FKs are too hard to use. I think I'm starting to cry.



  • @OhNoDevelopment said:

    At least you have FKs. Here, we don't use them because everyone thinks FKs are too hard to use. I think I'm starting to cry.

    So wait, you just have one giant table? First normal form to the extreme?



  • @Soviut said:

    @OhNoDevelopment said:
    At least you have FKs. Here, we don't use them because everyone thinks FKs are too hard to use. I think I'm starting to cry.

    So wait, you just have one giant table? First normal form to the extreme?

     

    You can still have multiple tables, and they can still have relations. They're just not enforced. And not visible unless you know the code, or can infer from the table and column names.

    Our database schema, setup before I was part of the company, is primarily MyISAM tables and there are lots of relations setup - purely in the code. This does lead to a lot of instances where data for some reason isn't there. And performance issues because MyISAM table locking is ridiculous. All in all, it works but it's incredibily ugly.



  • @OhNoDevelopment said:

    At least you have FKs. Here, we don't use them because everyone thinks FKs are too hard to use. I think I'm starting to cry.

    Used to be the same story where I work. Then I started adding FKs and when it came to code review time and people bitched, I simply said "Your claim that 'foreign keys aren't company standard' isn't, and never will be, a valid reason to not use them". My FKs remained, and from that point on all our new DB schemas used them.



  • That's the thought here, too, at least with some of our "legacy" applications. When I was a DBA (thank God that's over), I had an application developer accuse me of creating random FKs on his system. A data load had failed due to the FK, and that had NEVER happened before. Therefore the FK was new, and I did it to mess with them.

     Right.

    The actual reason was that a data push from an upstream DB had failed half-way through, and they just restarted it. Oops, key violations everywhere!



  • @OhNoDevelopment said:

    At least you have FKs. Here, we don't use them because everyone thinks FKs are too hard to use. I think I'm starting to cry.
     

    Around here the policy is to not use them either. As our leggacy code base gets bigger, I'm gathering arguments for using them, but I'm sure it'll take time.



  • @Mcoder said:

    @OhNoDevelopment said:

    At least you have FKs. Here, we don't use them because everyone thinks FKs are too hard to use. I think I'm starting to cry.
     

    Around here the policy is to not use them either. As our leggacy code base gets bigger, I'm gathering arguments for using them, but I'm sure it'll take time.

    What's the anti-FK argument? The only drawback I see is that they sometimes seem to get in the way during bulk data loads. That's easy to work around, though.



  • I find this mentality frequently in shops that just kind of learned "those database things" without really learning anything else.  Same with "developers" that only know SQL and use it for everything, often resulting in hacked-up solutions 100% reliant on the database to mimic real programming constructs.



  • Oh snoofle... if it weren't for you... how could I have a huge laugh.

    On a more serious note, have you considered giving "the powers that be" a taste of your ClueBat?



  • @Anarud said:

    Oh snoofle... if it weren't for you... how could I have a huge laugh.

    On a more serious note, have you considered giving "the powers that be" a taste of your ClueBat?

    As it turns out, another team was ordered to make their stuff faster, and they too, need to do partitioning. They are looking at doing exactly what I need to do. If they do, the keys can be the same everywhere, and we'll relax the aging-out requirement in favor of the make-it-faster argument (customers see the lack of speed; they don't see big honkin' database tables). Since we have a mandate from high, far and wide to make it faster, this might work in my favor.

    I will not be dropping any FKs, PKs or anything else that helps maintain sanity in this cluster-fsck of a db.

     



  • @ObiWayneKenobi said:

    I find this mentality frequently in shops that just kind of learned "those database things" without really learning anything else.  Same with "developers" that only know SQL and use it for everything, often resulting in hacked-up solutions 100% reliant on the database to mimic real programming constructs.

    Developers don't often realize that a database isn't something that you just throw SQL at. Heck, I sure didn't, because it rarely mattered. But I've seen a production DB repeatedly brought down by a crap application with no bind variable usage, resulting in bazillions of copies of the same select/update statements in memory. I'm trying to educate, and actually having a bit of success.



  • @Mcoder said:

    Around here the policy is to not use them either.
     

    Well, of course not. The damn things prevent primary keys from being deleted. Stupid idea.



  • @bridget99 said:

    @Mcoder said:

    @OhNoDevelopment said:

    At least you have FKs. Here, we don't use them because everyone thinks FKs are too hard to use. I think I'm starting to cry.
     

    Around here the policy is to not use them either. As our leggacy code base gets bigger, I'm gathering arguments for using them, but I'm sure it'll take time.

    What's the anti-FK argument? The only drawback I see is that they sometimes seem to get in the way during bulk data loads. That's easy to work around, though.

    Two big anti-FK arguments here are:

    1. It's inconvenient
    2. We don't know how to delete data with FKs turned on
    I once ran an automatic schema and entity relationship diagram generator on our database tables. Since nothing was relational, none of the tables could be linked. I had a bunch of lonely tables scattered about in my diagram without any connections.



  • @snoofle said:

    The powers that be decreed that the solution was simple: drop the FK constraints! If the code is written correctly, you shouldn't need them anyway. Since the code has long since stabilized, it should be a safe thing to do.
    You know the Thearac-25 had a cost reduction done to it removing the hardware safeties on the justification that the previous model operated safely thus proving the software safe and the hardware redundant which killed many people when an unknown error that had been previously been caught by the hardware safety (and the operator just rebooted the machine) caused the machine to irradiate the patient without the attenuation filter in place!



  • @OhNoDevelopment said:

    Two big anti-FK arguments here are:

    1. It's inconvenient
    2. We don't know how to delete data with FKs turned on

    Why then not fscking learn it? Oh, I forgot learning requires a functional brain. A rare gift these days.



  • @shimon said:

    @OhNoDevelopment said:

    Two big anti-FK arguments here are:

    1. It's inconvenient
    2. We don't know how to delete data with FKs turned on

    Why then not fscking learn it? Oh, I forgot learning requires a functional brain. A rare gift these days.

    What website are we posting on, again?

Log in to reply