Hi Jeff,
You are getting a lot of good advice.
Database Design for Mere Mortals is a wonderful book. It is not
FileMaker Centric. So it will stand you in good stead for an overall
conceptual understanding of database design in general.
You need to have your ducks in a row before you even start. This
involves having all of the appropriate entities defined first. In
general each entity corresponds to a table.
The attributes for each entity must also be defined. This corresponds
to the fields in that table.
The relationships between them should also be marked out. Some of
your reporting capability will be determined by the Entity/Attribute
and Entity/Relationship analysis you do prior to starting.
In a previous post, normalization was defined really really
succinctly. In the most general terms, if you have to enter it twice
or have to amend the data as time goes on, you are doing it wrong.
You must also relate to the real world and that world involves HIPPA.
If you go to FMForums.com:
http://fmforums.com/forum/fusionbb.php?
and scroll down to "Brain Food", there is a section on HIPPA. Just
going through the threads will give you a flavour of the problems
people are dealing with. Registering with that forum will give you
access to people who are dealing with it every day.
I am sure that there are also documents and official standards that
you can get.
I would also recommend getting at least one good book on FileMaker
Pro prior to starting this. I just picked up the FileMaker Pro 9
Bible by Ray Calgon with Dennis R. Cohen. It has great reviews. I am
actually starting though it today, so I can't comment to much further
except that when I was looking for a book to buy, (a)I went through
the table of contents and it does appear complete and (b) I have
purchased the FileMaker Pro x Bible for previous versions and I have
never been disappointed. Others may want to chime in here.
FileMaker Inc. promotes FileMaker Pro as a database for it's
"Legendary Ease of Use". Please do not be fooled. The closest
metaphor that I can come up with is the guitar - the musical
instrument with "Legendary Ease of Use". This ease of use propelled
the folk song era and early rock and C & W. On the rock based front,
then go up through Clapton, Hendrix, Zappa, Joe Satriani... Legendary
still applies, but ease of use is long gone.
Similarly, it is promoted as a database. That is one of the things it
is. It is a relational database capable of holding numerous tables
per file with an overall data capacity of 8 terabytes per file ...
Awesome, majestic and scary.
It is also it's own front end.
It is also it's own reporting tool.
It is also it's own server.
It is also it's own interface development tool.
Think of it this way, if you were doing this in MySQL, you would also
want to be conversant with PHP, other web programing, Apache, Crystal
Reports and a few other technologies.
You need a good reference on just what it is that this beast offers
and how it should be used prior to starting.
I'm not going to tell you that you cannot do this project. I will
tell you that based on the questions you are asking, you cannot do it
at this very moment without some serious preparation.
Here is the teaser. I know it can be done, because I have a client that did it.
The fellow who did it was director of operations at the time, really
bright and a joy to deal with. His solution is HIPPA compliant,
highly complex including an audit trail and seriously productivity
driven. I was called in to give complex staff/client scheduling
facility over multiple locations to their existing solution. So I did
get to look under the hood at a clinical management database that was
up and running. Not for the faint of heart.
Hopefully this post and the others give you some good guidance on how
to proceed.
Dave McQueen
--
...................
David A. McQueen
LICHEN Software
Barrie, ON, CANADA
www.lichen-software.com
(705)720-9022
