[KLUG Members] database programming question

[bill]

On Wed, 2004-06-23 at 10:06, Rusty Yonkers wrote:
> Just to redirect and refocus the thread a little bit....

Amen.

> First, thanks for the flamewar (I really mean it), I have gotten a
> lot of information from it.
> 
> Let me explain just a little bit more about our environment.  We have
> like 40 users on the system.  They are like totally not computer
> savvy (or even very technical).  

The more familiar the interface, the more they'll adapt quickly to it.
This gives you an advantage in writing something so they like it.

> The FileMaker 6 database has lots of
> information in it but the amount of transactions is fairly low in my
> estimation.  I do not think that I am going to have any performance
> issues from any of the solutions.

Absolutely.

> Currently we track clients in the system, what is wrong with them,
> what we are doing to cure them, and then follow-up with them
> afterwards to see if they are "staying on the wagon".

Sounds easy enough.

> The current system has multiple databases (it is like dBaseIII in
> that it uses a separate database for each table) which is good.  The
> problem is that the databases are not even close to normalized.  

This is your most important task, to normalize the database.  Do it on
paper so you can visualize it without worrying about the database
engine.  Normalize first and life is much easier.

> The
> system will gather information in one table.  Then when a record is
> created in a second table much of the information from the first
> table is copied into the second table when the record is created and
> then from that point on it uses the information from the second
> table.  Talk about duplication of data!  

Yes, I've seen lots of MS-ACCESS systems like this.  It's a horror to
keep all the data synchronized.  It involves an enormous overhead to run
something like this, and enormous effort to write programming or
manually keep synchronizing data.  If you normalize well, you'll never
have duplication.

> The users have full access to all fields of the tables at all times
> to change any data in any table.  The structure of the forms (layouts
> in FileMaker lingo) is such that we cannot lock any fields at any
> time if we ever want to do a find on them without duplicating every
> layout and creating a find routine for them and even then the users
> can easily get around that.  Obviously data integrity is right out
> the window currently.  Believe it or not though auditors have said
> that this is one of the best systems that they have seen! 

By that they surely mean it works and people are using it.  That is the
trump card for programming.  And, IMHO, means that you want to use the
sort of RDMS that accomplishes that the best.

> The report writer in FileMaker SUCKS!  To do calculations and sums I
> need to create fields in the database table that will do the
> calculation to be able to display it on the layout or report.  

With PHP you can write reports in your sleep (well, almost) and never
change the database.  You can make your supervisor look -really good-
when you write a PHP program to export the data into an Excel
spreadsheet for him to work with, make his impressive charts, etc.,
etc.  Click a button, out comes a spreadsheet (or a csv file, or an html
page, or a pdf file ...)

> The
> ODBC connectivity in anything before FM7 is not worth talking about
> at this point.  The new FM7 Advanced server that is suppose to be out
> next month is suppose to have full ODBC functionality.  FM7 also puts
> multiple tables in a single file finally.  FM7 will also give us more
> granularity with locking fields in layouts and still be able to do
> finds on them.  We could do the work in FM7 and that is a possibility
> but we will be paying $7000.00 for the upgrade, which for this place
> is a lot of coin.  

Keep your coins, go open source.  You can do search queries easily, as
well as put many tables in one database, etc.

> I kinda like the possibility of PHP but don't think that I could get
> all the functionality that I would really like in the system.  

Do you have specific ideas of what PHP could not do?

> I am
> really leaning towards using MySQL and RealBASIC for the system.  It
> looks like there are people that are doing that combination and using
> ODBC for the connection piece.  I am not adverse to PostGres but see
> much more written on MySQL so far (I may be looking in the wrong
> places though).  A copy of RealBASIC Pro is like only $400 so it
> would not be a really huge expense.  I looked on Amazon and it seems
> that there are a number of books on programming in RealBASIC too,
> that is a good thing.  The other nice thing with RB is that it is
> cross-platform, and yes I would love to give a presentation down the
> road if I use it since one of the platforms is Linux.

A big difference between PHP and RealBASIC will be support.  By that I
mean PHP has a huge base of people who have "done it before" and are
willing to comment on your code and offer suggestions.  Everything you
describe are things lots of us have done lots of times with PHP.

> I do know that if I can pull this off and get a working program from
> this that eventually they want to put a billing module in this too. 
> But I do not think that will affect the selection too much.  I think
> they want scheduling too and that might affect the selection. Hmmmm
> so much to think about.

With PHP, we commonly do billing and generate the invoices as PDF files
on the fly.  That way they can e-mail them, print them, fax them, save
them, search for them, etc.  Scheduling is pretty common, there may even
be one already written that meets your needs.  Looking them over may
help you understand how to write a custom one yourself.

kind regards,

bill