Date: July 2001
From: Victor
To: Frankly Speaking
Subject: Relational Database Design

Frank,

I am considering changing careers from a finance-based field to the world of relational database design and management. I have a very active involvement in the design of a major database for my company and I feel like I can break into this field easily. However, I am totally lost (without any education in programming) as to where to start. Which programming languages are a necessity in today's world: SQL, Java, Perl, Python, Visual Basic? How do they relate to each other? Which way to go? I have already dived into SQL but find that O'Reilly does not really have a book for beginning programmers to learn SQL.

Would you please give your recommendation on the leading edge languages in today's world of database design? And which books in your line-up are OK as tutorials?

Thanks,
Victor

Dear Victor,

I have always found designing a relational database to be an intellectually satisfying exercise and I understand your desire to move into that area. Not that I could ever do it, of course; I have to limit myself to brief bursts of intellectually satisfying exercises these days, for my health. I'm going to try to answer your questions, even though I'm an editor and a manager and therefore not to be trusted. I've copied several of our database editors on the mail, though, so they can send you corrections to my erroneous ideas.

First of all, let me say that database design is different from implementation and management, and it is quite possible to make a living (working for a large corporation or as a consultant) designing databases without ever having to program. My friend Joe, for example, is a data modeling consultant and he has not programmed since Fortran was a pup. In fact, he evinces a Platonic disdain for programming; it's disgustingly practical in his view and could delay his next modeling insight. Joe's tools are math, logic, abstraction, and metaphor. And yet he makes a tasty living.

If you do want to be involved with the implementation and the ongoing maintenance of your databases, though, I can offer this advice: Learn the languages and tools that the company you want to work for uses. I belong to the school that says a person can use any programming language to accomplish any task; but most companies standardize on a set of languages and tools and look to hire people who know them. So ask around at the places where you might want to work, find out what they use, and begin learning.

I can give you some general ideas of what works in which situations and what certain kinds of companies tend to use. Most big corporations have their important business data in Oracle databases. We have a whole line of Oracle books, some for programmers and some for administrators. If Oracle is your goal, you'll first want to learn PL/SQL, the Oracle programming language that works with their implementation of SQL. We have a whole line of books on PL/SQL.

I suspect that most Oracle programmers also use C or C++ as their basic language. You can't go wrong learning either of these languages. They're available on every platform and operating system that matters, and they're used in all kinds of programming tasks. We have just a few books on programming in C or C++. Another set of technologies that are gaining in importance with Oracle are the Java technologies. Java, from Sun, is an excellent general programming language for corporate and network computing, and its use is becoming integrated with Oracle. We have a whole series of books on Java programming, including the well-respected Learning Java. We'll be publishing a book in August about Oracle's new Java and database language called SQLJ (the "J" stands for "Java"): Java Programming with Oracle SQLJ. Oracle, SQL, C, and Java are great tools for breaking into (excuse the term) the database organizations of large corporations. Also, if you want to use Java to have access to most kinds of databases, including but not limited to Oracle, you can use Java's JDBC database access method. We publish a book about it, Database Programming with JDBC and Java, by the way.

Microsoft also offers a set of database products and languages that are used by many companies. I'm told that their products are less able to handle large and complex operations of the sort Oracle excels at, but that the Microsoft products are easier to use. The database Microsoft offers is called SQL Server. It uses a database language called Transact-SQL, about which we publish a book: Transact-SQL Programming. You might also read our ADO book: ADO: ActiveX Data Objects. ActiveX Data Objects are the universal way to access information in Microsoft databases. Also consider learning database programming using Access, a small and easy database product that nevertheless gives you a grounding in database techniques and programming. Our book Access Database Design & Programming would come in handy here. Visual Basic is an easy and ideal programming language for use with Access; we have lots of VB books to choose from.

Perhaps you're more interested in less formal programming and the Web. There is another set of technologies that are perfect for that kind of work, and, I'm pleased to say, usually available for free download. The two best-known open source databases are MySQL and PostgreSQL. MySQL seems to be in use more widely, but many people feel that PostgreSQL has more functionality. These two databases are very popular among those people who have database-backed Web sites. We offer a book on MySQL, MySQL & mSQL, and we'll have a PostgreSQL book soon: Practical PostgreSQL.

You can use a number of languages with MySQL and PostgreSQL, but the most popular are the three "P's," the open source scripting languages Perl, Python, and PHP. These high-level languages hide a lot of the complexity of database programming from you and enable you to implement your logic quickly and change it quickly, also, if necessary. Perl is the oldest of these languages and dear to the heart of O'Reilly, which publishes many books on that language, including Programming the Perl DBI, a book about how to use Perl to access database languages. Python is also popular with MySQL and PostgreSQL programmers. You can find database programming information in the new edition of Programming Python. PHP is the newest of these languages and ideal for Web programming. We publish a Pocket Reference for this language: PHP Pocket Reference. Watch our Web site, though; we've got a number of new books coming out in these areas.

I hope all this discussion was helpful to you, Victor, and I hope it spurs others to supplement what I've said. Let me say last that you'll be competing in the new field with programmers with masters degrees in computer science and years of programming experience. You should spend some time figuring out how your background in finance can help you become more valuable in a database environment. Remember that most commercial databases are just electronic implementations of the financial instruments that your current profession has used for generations. Good luck supplementing your financial knowledge with computing skills. You'll be a business colossus.

Frank Willison

Victor (and Frank),

I used to work as a DBA, and now I edit (and sometimes write) Oracle books for O'Reilly. I'd like to echo Frank's comment that database design and management are very different activities. You said you were involved in a database design effort for your company. Was that by chance a data modeling effort? Data modeling tends to be even further removed from actual database work than database design. Even if you get into database administration work, you'll find that there are many different types of DBAs, so you need to give some thought to your preferred working style, and other such things, before you jump into a career change. The following is some of what I've found in my own personal experience.

Data modelers: These people never touch a database, and certainly never program. They work at a high conceptual level and are deep into the business end of things. They work with business people to produce entity-relationship diagrams (and other diagrams) that show how to model business data. These diagrams usually get handed off to someone else to implement. You need to be good at interacting with people, facilitating group meetings, working with technical people, and you need to understand all the theory behind relational data.

Production database administrators: These are people who do actual work maintaining a database. They work on a schedule, have on-call rotations, carry beepers and cell phones. Production DBAs, especially for larger companies, don't do any design work. They implement what they are told to implement, or what a particular package requires, and then monitor, monitor, monitor. It's often said that the primary function of a production DBA is to be able to recover the database. There's truth to that. My experience is that this type of job tends to be somewhat routine. You wake up each day. You check to see if all your databases are running. You check backups. You look for space problems. You look for other problems. You do some reorgs. You go home. You do it all over again the next day. A good production environment will be routine and full of established procedure.

Development DBA: These DBAs support development environments and spend a lot of time designing and implementing new databases and database objects. This is where I have most of my experience. You may be given a data model and asked to design and implement a physical database from it; or, depending on circumstances, you may be asked to participate in (or lead) data modeling activities. You'll spend much time with developers working out SQL queries, optimizing SQL queries (making them run faster), perhaps helping to write PL/SQL code, and so forth. Development DBA work is not as routine, because development environments are inherently unstable. Development environments are often not considered mission-critical, so 24/7 availability is often not important. In all my years as a development DBA, I was able to avoid carrying a pager. That was probably a bit unusual, but I was happy about it. I don't like being on-call.

There's a great variation in DBA jobs from one company to the next. I used to work for a consulting firm, and as a result I got moved around to different projects and clients very frequently. I rarely had any type of routine last for more than a few months. If you work in-house for a regular company, your work would probably be a bit more routine than mine was.

You mentioned programming languages. These aren't really important, unless you are a development DBA who plans to write a lot of stored procedures. It's more important to learn basic DBA tasks such as how to create a database, how to backup and recover a database, and so forth. SQL knowledge (not a programming language) is important. In the Oracle world, PL/SQL knowledge is important (less so for a DBA than for a developer), and Java is growing greatly in importance. Oracle has a certification path for DBAs, and you can view a general list of certification requirements on Oracle's Web site. Working from that list of requirements would be a good thing to do if you want to become a DBA.

Best regards,
Jonathan Gennick