In my current job, dba's and programmers are divided in tasks. Any code that needs to be written in procedures dba's write, and programmers do only application code.

The strange thing is that whenever a task needs to be defined/specced, programmers get the task, and we have to define all the procedures needed and what they should return.

Is this a common practice in software development? Are programmers generally the ones tasked with building requirements for the database side?


Who else would you suggest should do it?

Think of it this way: Team A and team B work for the same company. If team A is required to provide an API for team B, who defines the requirements? Surely not Team A, without any knowledge of the application to be built on top of it. It must be team B.

Team A decides how to implement the API and they may insist on limitations for team B, but they cannot define what is required.

Does this change if the two teams are writing in different languages? No. So why should it change if team A is a DBA team?

  • 1
    +1 it's often hard to recognize a consumer party when the consumer is internal, people want to think it's a collaborator not a consumer, your example illustrates this relationship well. – Jimmy Hoffa Oct 2 '12 at 14:58
  • 1
    However, requirements != API. It makes sense that programmers would convey their data access requirements, but not necessarily that they would specify a list of procedures and the exact inputs and outputs of each. – Misko Oct 2 '12 at 15:11
  • @ChrisMiskowiec: What is the difference between data access requirements and exact inputs and outputs? – pdr Oct 2 '12 at 15:31
  • I think you make good points here. I tend to believe that if the scope is made known to both teams, creating requirements and specs can be made by either dba's or programmers. It seems since the db is lower in the stack, programmers get the bid because they are closer to the user. – Candide Oct 2 '12 at 20:29
  • @pdr: A data access requirement might be "we have an order number and we need to obtain a list of item descriptions"; an API specification would be "procedure GetOrderItems takes an integer parameter OrderNumber and returns a resultset with one column: ItemDescription". Think of it this way: Team A and Team B work for different companies. Team B can say they need a secure web service that provides XYZ, but it's not their place to say it must be a REST service with Basic auth over HTTPS, take ABC as a querystring parameter and return XYZ in UTF-8 encoded JSON. – Misko Oct 23 '12 at 14:42

I don't think it is common practice for DBA's to write stored procedures during the development of a program. Personnaly, I always had to write my own stored procedures, or sql code.

I think that since they know their database product much more than programmers (in theory, and at least it's true for me), they are best used whenever something that needs more analysis needs to be done on the database:

  • defining and optimizing table, tablespaces, indexes, etc.
  • optimizing stored procedures
  • database migration plan
  • etc. (DBA's, feel free to complete this list)

"Optimization" is the key I think.

A few years ago, I remember I asked my Oracle DBA about optimizing a procedure that was performing very poorly (like more than 10 seconds for only a few results). He came back to me with a request I did not understand at all containing keywords I've never seen before at that moment in time (INTERVAL and how to use partitioning are extremely powerful when put at good use) and that was giving the same good results in less than 25ms...

As for programmers, they have to conceptualize the database, provide the scripts and work together with the DBA so that the resulting database schema is most efficient for the required tasks. Programmers and DBA's should work together to identify/resolve bottlenecks occurring between the application and database servers.

  • It's actually quite common to have DBA's doing the sprocs as well, I didn't realize this myself with my first couple jobs until I ran into places where lots of developers don't actually know SQL very well. It's a real eye opener to learn how few developers know how to work through the entire stack including the SQL. – Jimmy Hoffa Oct 2 '12 at 15:23
  • @JimmyHoffa interesting, I have quite the contrary experience, thus my opinion on the matter. All developers I've worked with (event developers working on a front-end technology such as Flex) knew their SQL part enough to not need any help whatsoever. Is it possible that it is because I'm in Europe and it's different elsewhere ? – Jalayn Oct 2 '12 at 15:44
  • 1
    In the US, due to Sarbanes–Oxley Act requirements, it's becoming common for programmers working in large public corporations to be entirely isolated from doing any database programming work. While this degree of separation of duties isn't a legal requirement in all cases, many corps do a CYA by requiring it across the board. – jfrankcarr Oct 2 '12 at 16:18
  • @jfrankcarr interesting analysis! I've seen it both ways in companies but it definitely has grown towards less sql knowledge lately, this could definitely explain the current trend I've noticed. – Jimmy Hoffa Oct 2 '12 at 16:43

Is it okay for programmers to be given the task of outlining database requirements?

If the programmers are the ones who need a certain procedure, table, trigger, or whatever in the database, then it makes sense for them to give requirements to the DBAs and have them actually do the work, especially if the programmers are not very proficient in whatever stored procedure language is being used.

In my experience, many programmers also write stored procedures because this particular system runs mostly within the database and there simply aren't enough DBAs to go around. On the other hand, having the DBAs do the programming (or consult if they are too busy) can be a good thing as they may have insights on better ways to write performance-critical procedure code.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.