[KLUG Members] database programming question

[Adam Tauno WIlliams]

> > Connect to the backend using ODBC; 
> As long as you don't mind the extra overhead.

The overhead is absolutely trivial.

> > > 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.
> Define "casual" and "serious."  It can be done and done well with PHP &
> HTML.  If someone is typing in the data, it can be done with an HTML
> form.

Serious - filling out a data entry screen hundreds of times a day.  I
can attest that asking a user to do that on a web form won't make you
very popular.  And when you have many interlaced fields (values in field
A affect available values in field B) web forms get really tough without
Javascript,  and I really don't like Javascript.

> PHP & HTML also have several significant advantages:
> 	EASE OF USE: The user (almost always) already understands the
> interface.  This is no small issue when you have lots of users.  It is
> not a "new" program to them because they're familiar with the web.

Ease of use for the developer anyway.  Often not so easy on the user. 
For many things, ideal.
 
> 	CROSS-PLATFORM: The interface can be usable on any computer with a

Absolutely.  Unless you need to delve into things like Javascript in
order to make it usable.

> 	AVAILABILITY: The interface can be made available just about anywhere. 

Absolutely.

> > > We
> > > are a health care organization so I have HIPAA and other privacy
> > > regulations to think about.  > 
> By far the most important thing is to design the relational database
> well.  You should be able to do this on paper, no DB necessary.  Do that
> first.  

If your in a regulated or very litigious industry it actually is more
complicated than a well-designed database.  You always need to be able
to say (with under-oath certainty) that you know when X happened, and
that it was entered by Y, and that no Z is not able to enter X.  

> Avoid MS-Access.  Way, way too many problems that way.

Absolutely.

> > 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.
> But the man just said he wants to do it on a LAMP system.

NO, in fact, he did not.  He said a PHP/Apache solution was a possible
option.  He said he was surveying possible solutions.

> wait until they install fiber in his building just so they can handle
> "real" load?  Adam and MySQL, they just can't get along.  Just because
> MySQL can't do some outre thing should it always be treated as a leper? 

Sigh.  MySQL has some REAL flaws that affect it for ALOT of
applications;  why is pointing out those flaws a problem.  LAMP can't
solve everything, or maybe it can, but sometimes it shouldn't.  He is
surveying solutions.

> It might be fabulous for a particular application. 

Yes, it isn't even "might".  It "is" a fabulous solution for many
applications.  I have a MySQL database running 24hrs a day not six feet
away from where I'm sitting.  Works great.  It has a large amount of
data.  Never given me a moments trouble.  It is even accessed (by users)
via an Apache/PHP interface.

>  There is a reason it
> is enormously popular: it works, people can successfully use it.  If
> Adam's reasoning is correct, 100% of those applications are in error. 
> I'm not arguing that MySQL should always be used, but this argument is
> it should never be used.

Nope, use it every day.  In fact, I depend on a MySQL system to keep
track of the state of the entire network.  Our inventory data flows from
our business application to our extranet via another MySQL based
application, etc... 

> > 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.
> FUD.  Come on, Adam.  Is all this shouting necessary?

In a litigious or regulated industry this is not FUD.  You need to be
able to answer an auditors questions with 100% certainty.  That is why
so many database engines have built-in auditing capability (Informix,
DB2, Oracle, etc... have it).  You can 'hack' an auditing solution
together in PostgreSQL;  but it is easier to buy a database that support
it.  Thats why I recommend it.  Maybe this really doesn't matter for his
particular application.  But he mentioned HIPPA, etc... so I'm just
pointing out my experiences with dealing with these types of issues. 

Of course I doubt his FileMaker app addresses any of these issues.