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.