16

Every time I read about Oracle programmers etc, I get confused. I don't know what exactly they do.

From my understanding, application programmers need to develop the core functionality. The libraries they use might help in GUI development or database connectivity but the functionality that makes that application that application must be programmed and that makes every application different (well some could be tweaked versions of others).

In this relation, isn't database programming basically creating table and aren't these tables processed in response to SQL statements which are issued by an application which usually is the front end? So is table creation such a big deal?

Thomas
  • 169
  • 1
  • 1
  • 3

6 Answers6

22

To really appreciate what database programmers do you really have to have a go yourself - let me try and explain it another way.

To the ill-informed it might seem like in the ideal world application programmers don't really do that much - they take the requirements and processes as written by the business analysts and translate them into code that does the programmers bidding.

Of course anyone with any experience programming will know that this isn't how it works - ignoring for the moment the fact that the requirements never specify the behavior of the application down to the finest details, there are a number of complications

  • Programmers need to decide how the application should be structured.
  • Translating the requirements into something that a computer understands is often far from trivial.
  • Programmers need to be aware of the performance implications of different possible approaches.
  • As programmers gain experience using their platform of choice they become more proficient, delivering higher quality code at a faster rate.

(Of course this is a vastly reduced list, I'm just trying to pick up on points that have parallels in database development.)

Well, database development is much the same - to the ill informed it looks pretty straightforward, but once you get more involved you become aware of the specific complications of database development:

  • They decide how the database should be structured.
  • Often more complex queries can be far from trivial to translate from the requirements.
  • Database developers need to be concerned with the performance of the database.
  • They also need to be concerned with maintaining data integrity and availability.
  • And just like developers, database programmers become more proficient at everything they do as they become more experienced.

Just as application development is filled with hidden pitfalls (threading issues etc...), so is database development, and often the consequences of falling foul of these issues is very serious (e.g. data loss or potentially downtime for all applications using the database).

I think the thing that makes programmers think that there is nothing to it ("Can't a programmer do this?") is that there is a lot of overlap between the roles, and they do require similar skill sets. I've got no doubt that anyone who has the ability to be a good developer also has the ability to be a good database programmer given time and experience, however nobody should underestimate the value of an experienced database expert.

Boric
  • 103
  • 4
Justin
  • 1,728
  • 10
  • 16
  • Thanks for the reply(I had given up hope of getting one)! The reason I asked this is, I, as an "application programmer", designed a small database in msaccess to get something done for my project and it didn't seem much of a work but as a programmer of course I dbase thing isn't "easy". But I still lack the perspective needed to understand this. I mean, how different could one dbase be from another? Like no app dev "writes" file management code but uses libraries, aren't there ready to use templates/libraries available for dbase design? Or is dbase programming in reality dbase admin? –  May 04 '11 at 07:48
  • Wouldn't a typical programmer do this too? I guess I have worked in too small of organizations yet to observe any use for a database programmer exclusively since the application developer is usually the one driving the train, so to speak. He knows what he wants and usually designs it himself. – Engineer2021 May 04 '11 at 17:31
  • 2
    In my experience there are certain business actions that require _teams_ of expert DBA's. The most recent one I heard about was a merger between Coca-Cola and Minute Maid. Their databases (and they had a lot) had to be merged, and Minute Maid's wasn't designed the same way Coca-Cola had theirs and this, that, and the other thing. They planned and tested this merge for a good six months before pulling an all nighter to perform it. Having a standalone DBA isn't necessarily required in a small company, but in large ones teams of them become absolutely necessary for customer satisfaction. – Mike S May 04 '11 at 19:04
  • That said, in small companies (<50 people) having at least one (preferrably two or three) standalone DBA(s) is super, super nice to have as far as application developers are concerned. That and a dedicated IT staff to fix computers, but that's another story entirely. – Mike S May 04 '11 at 19:09
  • 4
    @0A0D, and all to often 6 years later when there are a billion or more records in the tables and when the whole mess is screamingly slow, they hire a database expert to fix the mess that should never have been designed by an application programmer. Databases are extremely difficult to refactor and need to be designed for performance from the start, something all too few application programmers seem to grasp. They also tend to design based on what the user interface needs not what the database needs and so omit internal controls and auditing and data integrity constraints, etc. – HLGEM Dec 16 '11 at 20:25
14

Database programmers do many things. First they design the database structure so that it will perform properly with the number of expected records. Design structures that work OK for a few thousand records can make a database unusable at a few million records. They also need to make sure the data will maintain it's integrity over time and that the data is secure from unauthorized changes or theft. They have to throughly understand normalization and when to denormalize and why. They have to understand performance and how to ensure data integrity. They have to understand security and how to prevent data from being stolen or maliciously changed.

They performance tune queries. I have changed queries that take minutes to run to millseconds. I have changed a process that took over 24 hours to run to less than 30 minutes. They design and maintain indexing structures that will balance the speed of inserts against the speed of selects.

They write complex queries especially reporting queries. I personally have written queries that are over 1000 lines long due to the complexity of the requirement. They still had to and did run quickly.

They create data warehouses and the accompanying ETL processes to support them. Often they need to write processes to bring data in from other sources and have to figure out how to map the fields from some clients data base to theirs and these are never a close match in data type, data size, required fields, lookup values, etc.

They have to determine how to refactor as the database requirements change without harming the 100,000,000 records they already have and without bring the usage of the database to a complete halt. Large databses can involve thousands of tables and stored procs and user-defined functions. Understanding such a structure takes time and skill as does understanding what will be impacted by changes and how.

They design ways to audit the data for regulatory and recovery reasons. They then design ways to recover the data from those audit tables. They research problems with the data to find if the problem was from a bug in the import process, a bad file provided by others or a bad insert/update from the application, or from unauthorized access. They find ways to fix the bad data when the application programmers left open a hole for hackers to attack.

Often they are involved in data conversions from one system to a new system. Sometimes this involves moving data from one COTS product to a new one that the company just purchased. Like the imports described earlier, these are complex processes which can take months to plan for and execute and which require extensive testing. Unlike the imports, the database programmer may have no control over the disparate data structures.

HLGEM
  • 28,709
  • 4
  • 67
  • 116
8

I interned as a database programmer for the manufacturing data of a 24-hour wafer fab in the late '90s. I don't know how typical my duties were, but the largest part for me was when a change to field encoding or schema was necessary, I had to make sure that the change was seamless to production. Essentially, that meant I would tell them to upgrade their client application, which they would do at a time convenient for them, and it was expected to come back up immediately with the new changes.

That was a lot more involved than I had anticipated. Conversion scripts and client software had to be tested thoroughly. Often two semantically identical but incompatible sets of data had to be maintained in synchronization until everyone was switched over. Sometimes it was necessary to make the switch in multiple, carefully planned phases in order to make it seamless. It wasn't uncommon to prepare for weeks for a switchover that happened essentially instantaneously.

If a database programmer is doing his job right, it will seem to observers like his job is very easy. I'm not surprised that many people don't really know what they do.

Karl Bielefeldt
  • 146,727
  • 38
  • 279
  • 479
2

This is rather simple. If you heard about like the MVC pattern you should know the difference between your controllers and models. For example if you are writing an ERP then imagine that in your controller you just say "retrieveCashFlow" to your model and your model calls a stored program in the database. This stored program takes care of every join, filtering, ordering and so on and you get processed data back. In your controller you just have to mush things together.

If you have doubts about stored procedures check out this: why use stored procedures?

Simply put: database developers write stored programs (procedures and functions) for your application to take care of the M in MVC (or the business logics if you dont use mvc).

Adam Arold
  • 1,190
  • 1
  • 8
  • 23
2

Oracle is not just a database but a complete programming environment, including forms and report designers. As an Oracle programmer, you program complete user applications. The database coding you refer to would often be done by specialist Database Adminstrators (DBA's).

Sybase I think is another one with a similar programming environments.

Other databases may restrict themselves to "just" allowing the definition and execution of reports, whereas yet others may not offer any form or report design/execution facilities at all.

Marjan Venema
  • 8,151
  • 3
  • 32
  • 35
  • 1
    "Oracle is not just a database but a complete programming environment...Other databases may restrict themselves to "just" allowing the definition and execution of..." This I didn't know. Now it makes sense. – Thomas May 04 '11 at 07:56
  • SQL Server is the same. Besides the stored procedures, the SSIS packages allow visual programming, calling other existing code, writing vb.net or c#.net programs, and lots more, all wrapped up in an IDE. I haven't used SSRS, but I suspect it's similar. There is a TON of programming, and a database programmer has to know lots of different tools and languages and processes. – thursdaysgeek May 04 '11 at 20:00
2

I would say a database developer is responsible for one or more of the following

  • Design, this includes creating (or rather defining relations) tables
  • Optimization, setting the proper indexes, choosing keys, choosing the right data types
  • Functions, writing useful functions to use in the queries
  • Procedures, writing application logic that is tightly coupled to the database layer.
  • Creating trigger functions to respond to events
  • Producing specifications of the above.

Depending on the RDBMS in question it could include tasks such as

  • Creating reports and forms
  • Creating flows for data import/export

Have a look at this list of responsibilites

Peter Lindqvist
  • 131
  • 4