[KLUG Members] database programming question

[bill]

I've been away so I'm late to this discussion.  But this is what I
primarily do all day every day so I'm eager to contribute to this
thread.

On Tue, 2004-06-22 at 08:02, Adam Tauno Williams wrote:

> Connect to the backend using ODBC; 

As long as you don't mind the extra overhead.

> > 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.

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.

	CROSS-PLATFORM: The interface can be usable on any computer with a
browser.  The designer of the HTML can make something that works for
anybody.  I'm meeting tomorrow with a new client who saw a current
client's work.  The current client couldn't get his computer to work at
a conference.  So they used Client B's Macintosh.  They were "blown
away" (their words) at how easy and well it worked.  Linux, MS, Mac,
they can all work with an HTML based system and server side programing
like PHP.

	AVAILABILITY: The interface can be made available just about anywhere. 
Run it internally on your LAN, run it on a web server with appropriate
security if you need to have a wide range of people use it.

> > 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'd sign up for the PHP General and the PHP DB newsgroups.  Many of the
same people are on both.  Very helpful.  

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.  

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

> 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.  

I agree, MySQL is easy and can handle huge loads well.  It's also quite
robust.  I've seen lots of other things break on servers, MySQL is
extraordinary tolerant of outside aberrations.  

> 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.  Should he
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? 
It might be fabulous for a particular application.  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.

If we could install MySQL on the KLUG server, I'd be happy to do a class
on doing HTML forms with PHP and a MySQL backend.

> 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?

The author says "I do not have a large amount of experience or training
in database design or programming" along with "oh and it is part of my
job to get this fixed."  

He has a Filemaker system with no normalization. It is "awful and breaks
many rules of relational database design."  Yikes.  That's where his
greatest task lies.

His first thoughts about PHP and MySQL on a Linux box are good because
going that route is likely to make things better.  He may actually get
it done and have something that works.  

This route is the -least- likely to result in the too common scenario
(think of MS-Access) where he puts in enormous hours and never gets
something working.  He says "I know that with hard work I can pull this
off."  LAMP is the way that's most likely to happen.

kind regards,

bill