[KLUG Members] database programming question

[Adam Tauno Williams]

> I have suggested that we do a new program from the ground up so that
> it can be better structurally.  I have suggested that we do it with a
> SQL backend and then a GUI front-eand.  I am thinking of doing the
> backend in MySQL and not sure yet what to do the front-end in.  The
> front-end program is for people using Windows 2000 or XP

There are lots of RAD system to try - either .Net/Mono or using
OpenOffice would give you a nice and portable client.  I actually
wouldn't rule out using OOo since then you also get the transparent
ability to generate spreadsheets, output merge documents, etc... with
trivial extra effort.

> workstations.  I am thinking of putting the database server on a
> Linux server backend.  

Connect to the backend using ODBC; use whatever engine you want, don't
paint yourself into a corner.  Also I'd leave final backend selection
till the last thing as issues tend to turn up - and I don't know what
your data is but various engines all have various extensions available
that can let you do some really interesting things with little
additional programming.

> Part of me is thinking that I just left reality and entered the
> twilight zone but I know that with hard work I can pull this off.  I
> am not sure what books or other resources to look for though for help
> in doing this.  I am also wondering if I would be best to do like a
> PHP web front-end 

Web interfaces are good for casual maintance or for querying data,  but
really *BITE* for serious data entry.

> or maybe a character based front-end and use ssh
> for the terminial emulation of it, at least for basic data entry.  

That could be hard;  most RAD tools these days don't even offer curses
as an output option.

> We
> are a health care organization so I have HIPAA and other privacy
> regulations to think about.  

Right, that makes it extra fun. :)

> Also, does anyone know of a good newgroup or email group that I could
> get on for database programming discussions?  I hope this does not
> start a flamewar about MySQL and Postgres.  I am not like dead set on
> MySQL but think that it may be easier and also would easily handle
> the workload.  

"Easier" than what?  Just about every RDBMS is just as easy to 'deal
with' on a day-to-day basis as any other (with the exception of Oracle,
which is brutal).  You'll face the same basic issues - you have to deal
with logs, update statistics, etc...  Assuming that you have any 'real'
load.  I live with Informix, MySQL, and PostgreSQL every day.  IMHO
dealing with under real load from easiest to most difficult is - (easy)
Informix, PostgreSQL, (harder) MySQL.  ***BUT** the gap between them is
pretty small and mostly has to do with the support tools and the
orientation of the documentation that anything to do with the actual
engine.   Informix (a commercial db) is the best because it has AWESOME
tools for backup, restore, log rotation, session management, etc... 
MySQL is the 'worst' because the documentation has very very serious
LAMP slant and is blind to almost all else.

Also MySQL can't authenticate via PAM or much of anything else.  So
there is another credential (username/password) repository to deal
with.  Then people end up going with the approach (natural for LAMP)
that the app authenticates with it's own (well known) credentials - this
is TOTALLY unacceptable from a security and auditing standpoint.  Your
database MUST record auditing information (your a HIPPA organization
right?) so users MUST authenticate to the database individually and the
engine itself MUST record auditing information.  It isn't sufficient to
'leave it to the application'.  So that is one consideration - and MySQL
in 4.x may have completely dealt with these issues, I don't know. 
Auditing requires (a) external authentication integration and (b)
triggers [ to record the auditing information RELIABLY ].  That is of
course, unless you enjoy auditors and lawyers breathing on the back of
your neck.

> I am thinking of talking with the organization though
> and asking if I can have the rights to market the application to
> other organizations like ours after I write it with our organization
> having free use of the application.  I know that MySQL is suppose to
> have a commercially paid license if it is used for an application
> that is sold, and I do not have a problem with that but wonder if
> other SQL backends are the same way.

PostgreSQL is free for commerical use.  You can get a lite license for
DB2 really cheap,  and DB2 is a great database with great support
tools.  If you like going on vacations I'd recommend a nice commercial
database with point-in-time log restoration, etc...