Articles   Home

Basic Database Design for Database Software Administration

The last article had basic design topics involving the operating system administration, this article covers the database administration topics for designing database software. Many of the topics covered under system administration have parallels in database administration. Each topic covered in the companion article will be addressed from the database administration perspective. None of these topics are cutting edge, but it seems most applications miss some of the basics.

On a real RDBMS most applications should have their own administrator user that 'owns' the schema. Do not stuff the application into the RDBMS system schema or other application schemas. Let the DBA designate the application schema data spaces, do not constrain the schema to a specific place. Let the DBA designate the schema owner, do not constrain the application to a specific naming space, but a recommended taxonomy is not a bad thing. This schema owner should be used to change the application schema and do administrative roles, for example, granting privileges to schema objects.

The database user that uses the application should not be the schema owner. This usually indicates severe design flaws, especially in security. The user privileges needed to run the application should be follow the principle of "least privilege", in other words be the lowest level possible. Use roles to control sets of privileges given to database users.

Have a method of installing different schemas and versions so as not to crush previous installations. Also have a method to remove the application without destroying the underlying database product operating system, system utilities (like archive) and other applications.

Have a method of turning on and off the damn thing and include a script to do so. Patching or fixing data is hard if the application is running, it can only run 24x7 and if it fails, it fails ugly and leaves a heap of scrambled data.

Home grown license managers, cryptography, security, etc should be avoided. Use standard APIs or products that do these things better than you ever could. Concentrate on what the application should do well, not on a better password scheme, unless a password scheme is the application.

Design so that this specific database application is not be the only thing running on the RDBMS. Too many applications force RDBMS to do unconventional things for just this one application. When several more applications are installed on the RDBMS with the same premise, they just don't play well together. An RDBMS is a data store that can and should be accessed by many applications, not an isolated thing.