FKing the Tables



  • We have an app that runs "Direct Debit" for various services. This institution has something around 100+ branch offices, each one with its own DB server; while we have a "Central Office" DB thats updated daily, however this is done during the night. It usually isn't a problem, as most of our billing processes are done on the actual "branch" DB; and service registration is usually done by existing customers.

    So the latest and greatest service that we were tasked to release was geared towards brand-new customers. As they open an account, they're offered this service... which was nice, but it meant our Central DB wouldn't find that info. Solution: we did our checking on the branch DB, then proceed as usual.

    Suddenly, about 2 weeks after rolling out, users start to complain about the registration procedure. After wading through the logs, I find this:

     com.sybase.jdbc2.jdbc.SybSQLException: Foreign key constraint violation occurred, dbname =  'xxxxx', table name = 'xxxxx', constraint name = 'FK_xxxx_xxxx'.

    Turns out some brillant DBA decided that we had not placed the right Foreign Keys and 'helped' us by putting them in there, when in fact, he screwed up our app, as the new accounts are not even in the central database!!!!



  • @danixdefcon5 said:

    we have a "Central Office" DB thats updated daily, however this is done during the night



  • @danixdefcon5 said:

    Turns out some brillant DBA decided that we had not placed the right Foreign Keys
    Your fault; if you didn't document why an expected FK constraint isn't there, how is he to know that you specifically intended it to be missing? Competence is not clairvoiance; he cannot divine your intent and so must make assumptions if not given specific directives. Unless you envision the role of DBA as a monkey given a banana each time he copypastas from e-mail to query browser...@danixdefcon5 said:
    and 'helped' us by putting them in there,
    His fault. Making changes to a live, production database should only be done with the approval of the system's architect; he should have asked first. His assumption was incorrect which led him to break things.

     Documentation is f***ing annoying, but it is there for a reason. As are Standard Operating Prowtfs.



  • @danixdefcon5 said:

    Turns out some brillant DBA decided that we had not placed the right Foreign Keys and 'helped' us by putting them in there, when in fact, he screwed up our app, as the new accounts are not even in the central database!!!!
    You know, sometimes a user's avatar and his post match perfectly :)



  • @TwelveBaud said:

    @danixdefcon5 said:
    Turns out some brillant DBA decided that we had not placed the right Foreign Keys
    Your fault; if you didn't document why an expected FK constraint isn't there, how is he to know that you specifically intended it to be missing? Competence is not clairvoiance; he cannot divine your intent and so must make assumptions if not given specific directives. Unless you envision the role of DBA as a monkey given a banana each time he copypastas from e-mail to query browser...
    Umm... that's pretty much exactly what our DBAs do.  They're not our data architects.  They're just a step we have to go through to get things into QA and Production.

    Then they administer the database, creating users, roles, and refreshing our QA database every once in a while.  Every once in a while they email us to tell us our queries are taking too long and bogging down the system.  



  • @DOA said:

    You know, sometimes a user's avatar and his post match perfectly :)
     



  • @TwelveBaud said:

    @danixdefcon5 said:

    Turns out some brillant DBA decided that we had not placed the right Foreign Keys
    Your fault; if you didn't document why an expected FK constraint isn't there, how is he to know that you specifically intended it to be missing? Competence is not clairvoiance; he cannot divine your intent and so must make assumptions if not given specific directives. Unless you envision the role of DBA as a monkey given a banana each time he copypastas from e-mail to query browser...
    Actually, it was documented. The DB structure was documented even before we made the new tables.

    The FK fubar was done by a newcomer DBA who didn't even tell the other DBA's what he did.

    @TwelveBaud said:


    @danixdefcon5 said:

    and 'helped' us by putting them in there,
    His fault. Making changes to a live, production database should only be done with the approval of the system's architect; he should have asked first. His assumption was incorrect which led him to break things.

     Documentation is f***ing annoying, but it is there for a reason. As are Standard Operating Prowtfs.

    One of my favorite WTFs over here is how some DBA's manage structure changes. It usually involves:

    DROP TABLE something;
    CREATE TABLE something;

    followed by the app crashing & burning because they forgot to GRANT privs on the new table. Oh, and at least once they forgot to re-create the indexes on one of those tables...



  • @DOA said:

    @danixdefcon5 said:

    Turns out some brillant DBA decided that we had not placed the right Foreign Keys and 'helped' us by putting them in there, when in fact, he screwed up our app, as the new accounts are not even in the central database!!!!
    You know, sometimes a user's avatar and his post match perfectly :)

    My avatar usually matches my expression every time I'm handed someone else's code, or when a new project is given to me, especially those with interesting requirements.


  • @TwelveBaud said:

    Your fault; if you didn't document why an expected FK constraint isn't there, how is he to know that you specifically intended it to be missing? Competence is not clairvoiance; he cannot divine your intent and so must make assumptions if not given specific directives. Unless you envision the role of DBA as a monkey given a banana each time he copypastas from e-mail to query browser...

    This is just absurd.  You sound like one of those documentation freaks who believes writing every single thing down will make their software and systems perfect.  Then you defend the actions of a DBA who enacted a change to the design and business logic without asking anyone or documenting it.  I don't think the lack of FK should have been documented and DBAs should not modify the design and business logic arbitrarily. 



  • @morbiuswilters said:

    This is just absurd.  You sound like one of those documentation freaks who believes writing every single thing down will make their software and systems perfect.  Then you defend the actions of a DBA who enacted a change to the design and business logic without asking anyone or documenting it.  I don't think the lack of FK should have been documented and DBAs should not modify the design and business logic arbitrarily. 

     

    DBAs are monkeys. Most of them have little training but act like they are the guardians at the gates of heaven. 



  • @TwelveBaud said:

    @danixdefcon5 said:

    Turns out some brillant DBA decided that we had not placed the right Foreign Keys
    Your fault; if you didn't document why an expected FK constraint isn't there, how is he to know that you specifically intended it to be missing? Competence is not clairvoiance; he cannot divine your intent and so must make assumptions if not given specific directives. Unless you envision the role of DBA as a monkey given a banana each time he copypastas from e-mail to query browser...@danixdefcon5 said:
    and 'helped' us by putting them in there,
    His fault. Making changes to a live, production database should only be done with the approval of the system's architect; he should have asked first. His assumption was incorrect which led him to break things.

     Documentation is f***ing annoying, but it is there for a reason. As are Standard Operating Prowtfs.




    I both agree and disagree with you. Its his fault for lack fo documentation. Its the DBA's fault for not consulting the coders before adding a constraint when it was completely untested. He should have sent emails, gotten OKs from the team, then added, not the other way around where the customers do the emailing.



  • @astonerbum said:

    I both agree and disagree with you. Its his fault for lack fo documentation.
    Except that he explained a few posts up that it was documented.  



  • @danixdefcon5 said:

    Actually, it was documented. The DB structure was documented even before we made the new tables.

    The FK fubar was done by a newcomer DBA who didn't even tell the other DBA's what he did.

    Ah! Well then that DBA deserves to be reassigned to helldesk. Permanently. If he can't be arsed to read the docs, he can't be arsed to have a job.



  • @morbiuswilters said:

    You sound like one of those documentation freaks who believes writing every single thing down will make their software and systems perfect.
    Most of the work I do is interoperability reverse engineering. Things like "I have this thing in Project Server with a custom field, and I want to get the custom field back, and I don't want to use the API cuz I just don't. Help??" Having a functional spec of how Project Server saves its custom fields, for example, saves me from having to grovel through the databases and write it myself.

    Even ignoring my niche, some documentation is still useful. There are exceptional circumstances where things which normally are done aren't, and having an explanaiton there shows that it was a conscious decision rather than an oversight.@morbiuswilters said:

    Then you defend the actions of a DBA who enacted a change to the design and business logic without asking anyone or documenting it.
    There was a second part to my post, you know. I think that the DBA screwed up badly. He should have asked someone for sure, and if there was a spec he should have changed the spec. I think he's a complete and total fool and should not have a job. I just recognise that he might not have been solely to blame; some of the programmers I work with think nvarchar(50) is T-SQL's only datatype, and others usually don't add indexes because they just don't remember, things which it falls to me to clean up when my boss gets yelled at for our apps being slow. Since this system was documented, and was designed well before the DBA got involved, it is all the DBA's fault.

     


Log in to reply