Healthcare database construction

[georgesmith]

Good Morning. I am new to database design/development and do not have any software development experience whatsoever. I hope this is the proper area of this forum to ask the questions I have, as I was intrigued with DACM's thorough and comprehensive review of DB engines.

I have a tiny healthcare practice that consists of myself and one admin support person at present. I am keenly interested in developing a database solution. My most pressing and immediate needs are to streamline my paperwork flow, track patients, patient data, and be able to generate reports to providers, insurance companies, etc. My primary needs for a database solution are as follows:

Rapid development and deployment
Highly secure and tight control on who has access to what patient data within the database
Easy input/output of data to other sources if necessary
Relatively easy to design and build
Highly scalable if necessary


I had seriously considered Filemaker Pro, but it fails on two points: Cost of deployment/scalability, and it does not output data should I need to in the future, to other database solutions easily (e.g., Postgres) as near as I can tell.

As I am not a software person, I need a solution that a total neophyte with limited time can easily learn and deploy and as new needs arise, easily modify and add features to the database.

I currently use OpenOffice and am intrigued with the possibility of using Base as a front-end to create/manage a database, design forms, and create reports. The imbedded solution will not work as I need a server environment. From reading online and reviewing the forums here, I have learned that it is easy to extract the HSQLDB and use in a server mode, which is awesome. But after much review of potential DB engines, I'm unclear which one will meet my objectives above using OO Base as the front-end. The engines I'm considering are:

HSQLDB (v2)
H2
Postgres

I like the fact HSQLDB and H2 have a small footprint, secure, are very fast, and can be used in-memory. My concern is how easy is it to input data from other sources and more importantly, export all data to another database relatively easily/painlessly, should the need arise. While I'm aware HSQLDB and H2 will work great for my needs at present, I am hoping to expand my practice such that there could be as many as 100 individuals or more (between providers and support staff) accessing the DB locally and remotely. Can HSQLDB and H2 scale to this level and keep humming along? At which point do they start to bog down? I'm aware Postgres will handle pretty much whatever I throw at it, but am concerned how easily it is to develop and use as a backend with Base. For example, with Base, I can create the tables, relationships, fields, etc. and it takes care of setting up the backend in HSQLDB. This may be a dumb question but as a newbie, I am wondering if I were to use Postgres as the backend for Base, will it do this as well? What are the pro/con's of such a set up vs using H2 or HSQLDB.

I would greatly appreciate any feedback and help.

[Villeroy]

Base is simple and simple is the opposite of easy. What evolved as a separate "Base" component since OOo 2.0 used to be mere database connectivity in the preceeding versions of OOo and StarOffice. Anything tabular (including spreadsheets, text files and your Mozilla address books) is connectable as a "database" so you can use the contained data in office documents as bibliographies, address sources for serial letters and labels, input for calculation models or even tables in presentations.
It's all about pulling database data into office documents in order to get data in a printable form (reports).
Additionally, there are some very, very simplistic input forms. Evverything is tightly related to printable office documents.
The entire office suite is built around the ODF standard for office documents and database connectivity is just a supplement to meet more professional requirements. Base has not much in common with database development suites such as MS Access, Filemaker and Paradox.

Rapid development and deployment is a matter of the underlying database backend. A Base frontend is the opposite of "rapid development".
Highly secure and tight control on who has access to what patient data within the database has nothing to do with the frontend. Base supports the usual mechanics of groups, users and name/password log-ins.
Client related security is a myth. This is always obliged to the server.
Easy input/output of data to other sources if necessary. Base edits one data source at a time.
Relatively easy to design and build. Nope. Base is too simple to be easy.
Highly scalable if necessary. Database backends are scalable. Base may be connected to some scalable database or not.

[georgesmith]

Hi Villeroy:

Thank you for your quick response. I'm a bit confused when you say Base is simple and simple is the opposite of easy; that Base is too simple to be easy. Can you explain? Again please excuse my naivete and novice understanding.

I am interested in pulling DB info into docs as you outlined. Could you elaborate on why Base does not have much in common with MS Access, FMP, etc.?

I think I understand your comment about scalability pertains to the backend. However, I want to split out the HSQLDB (or consider using H2 or Postgres) and use OO Base to create forms, etc.

In advance, many thanks for your time.

[DACM]

...I was intrigued with DACM's thorough and comprehensive review of DB engines...

[Solved] Database engines
...link provided since this post was moved from a 'Solved' thread.

[DACM]

Welcome georgesmith,

Wow! You've done your homework on these forums. It's encouraging to know that these informed questions came from a "neophyte" with little or no software development experience.

But at the same time, we may be making it sound too easy. I'm sure you're aware that database application development is not very easy. It involves relational database design-concepts, separate database engine setup for stable/scalable configurations, learning some SQL, and mastering various tools including the front-end development environment. And while creating a basic, desktop, database application is possible using only the Base tool-set and built-in database engine, your eventual requirements are not so basic. And as Villeroy mentions, Base is too simple (simplistic and under-developed) to be simple (easy) for creating a full-blown database application. However, despite the shortcomings of the Base tool-set, a database application can be produced given enough time and sheer perseverance. It's quite time-consuming to learn Base concepts, features, quirks and limitations.

My advise for someone that's new to software development and particularly to database application development: go with Microsoft. M$ isn't free ($117), but neither is your time. MS Access is 10x easier than Base, much prettier, better supported, and provides far more front-end features in a true Rapid-development environment. It provides an optional split-database environment for stability and maintainability -- just like Base. Plus it provides migration and scaling to MS SQL Server (of course). And recently, MS Access 2010 allows you to save your desktop-forms as web-forms for remote, browser-based, database access/interaction. I'm not sure about web-reports or the many details of these web-centric features because I haven't done a project with MS Access since 2007. But for the cost-conscious, I do know that MS Access supports up to ~10 users with a single, licensed copy of MS Access due to the free runtimes (2010 / 2007) available for client machines. It's possible to migrate from MS Access (Jet/ACE engine) to PostgreSQL, while still using MS Access as the front-end. But if PostgreSQL is your goal, you should start with it as the backend for MS Access. That would save the licensing fees as you scale to 10+ users and remote (web-browser) access.

[DACM]

As I am not a software person, I need a solution that a total neophyte with limited time can easily learn and deploy and as new needs arise, easily modify and add features to the database.

Keep in mind that this comes across as saying, "I'm not a pilot, but I just need some quick tips in the event that I need to land a 747 safely through stormy weather with an engine-out someday."

HSQLDB and H2...My concern is how easy is it to input data from other sources and more importantly, export all data to another database relatively easily/painlessly, should the need arise.

Well again, no promises on painless. These two Java engines are closer to the SQL standards than any mainstream engine but that doesn't mean it's painless. For the tables and data, there are some commercial and open source migration tools available to aid in this task. But tables and data are only part of the equation. You may need to re-work data-types or re-create auto-incrementing fields, indices, etc. Even your front-end components (forms, reports, even queries) will often break when adopting a new engine due to things like syntax and schema support. Best to choose your final backend up front -- but like you mentioned, this involves a steeper learning-curve, quickly involving raw SQL. With PostgreSQL (MySQL, Firebird, Oracle, MS SQL Server, etc.) you'll also need to manage the backend separately on a daily basis (start/stop/backup tasks). But then, these are necessary tasks when running any database engine in server mode.

...as many as 100 individuals or more (between providers and support staff) accessing the DB locally and remotely. Can HSQLDB and H2 scale to this level and keep humming along? At which point do they start to bog down?

I'd be guessing. Multi-user concurrency, in this case, probably comes down to the connection characteristics (handling many continuous connections versus many intermittent/state-less connections), threading, and perhaps other factors. On the other hand, commercial web services and software use these Java databases with success, and even express professional-preference over MySQL and PostgreSQL in some cases. For instance Razuna was designed and built on H2, but now offers other backend choices as options. Again, that's the purpose of the 'Database engines' post, so you can choose the best solution for your requirements, perhaps within the bounds of your abilities.

[RoryOF]

As I am not a software person, I need a solution that a total neophyte with limited time can easily learn and deploy and as new needs arise, easily modify and add features to the database.

Keep in mind that this comes across as saying, "I'm not a pilot, but I just need some quick tips in the event that I need to land a 747 safely through stormy weather with an engine-out someday."

I agree with DACM and have privately told georgesmith that he should look for some shareware/freeware solution. A suitable solution ought allow him to interface to it for any specialised reports he needs. Designing and implementing a database is a non-trivial task (very non-trivial!); it is a fascinating intellectual exercise and we would not deny that, but it fragments the focus of any healthcare professional. My advice is that Georgesmith should find some system that does much of what he needs and get using that immediately; perhaps in his spare time over an extended period he may arrive at a bettter solution, but at least in the interim he would have a solution that handles a large percentage of his requirements. The important matter to determine is if the underlying database engine is externally accessible, rather than being in a proprietary format.

[georgesmith]

Welcome georgesmith,

Wow! You've done your homework on these forums. It's encouraging to know that these informed questions came from a "neophyte" with little or no software development experience.

Hello DACM: Many thanks for your kind words.

But at the same time, we may be making it sound too easy. I'm sure you're aware that database application development is not very easy. It involves relational database design-concepts, separate database engine setup for stable/scalable configurations, learning some SQL, and mastering various tools including the front-end development environment. And while creating a basic, desktop, database application is possible using only the Base tool-set and built-in database engine, your eventual requirements are not so basic. And as Villeroy mentions, Base is too simple (simplistic and under-developed) to be simple (easy) for creating a full-blown database application. However, despite the shortcomings of the Base tool-set, a database application can be produced given enough time and sheer perseverance. It's quite time-consuming to learn Base concepts, features, quirks and limitations.

This is helpful. Thank you.

My advise for someone that's new to software development and particularly to database application development: go with Microsoft. M$ isn't free ($117), but neither is your time. MS Access is 10x easier than Base, much prettier, better supported, and provides far more front-end features in a true Rapid-development environment....

Ahh...That sounds good. A friend and colleague--who prior to going into healthcare---was an engineer, also recommended MS Access. However, I am using a Mac to run my practice and unfortunately, MS did not write Access for OS X

[georgesmith]

As I am not a software person, I need a solution that a total neophyte with limited time can easily learn and deploy and as new needs arise, easily modify and add features to the database.

Keep in mind that this comes across as saying, "I'm not a pilot, but I just need some quick tips in the event that I need to land a 747 safely through stormy weather with an engine-out someday."

(laugh) Fair point DACM!! Well, you can't fault me for trying to have my cake and eat it too!! Seriously, I realize I have some learning ahead of me, but from all the responses in the last 24 hours, I have realized I have not clearly articulated what I'm trying to do, so my apologies. I also tend to think big and like to keep all my options open as my practice grows.

First off, I'm not a physician. I specialize in helping patients manage their chronic pain and I do a lot of work with chronic insomnia. I write lots of encounter notes, initial diagnostic evaluations, and track data I collect (e.g., sleep logs) in a spreadsheet. Presently right now, I have to manually transfer the spreadsheet data to the document template that makes up part of my encounter note. To time consuming. I also track in an off-the-shelf db for the Mac (Daylite), all the contact info that is merged into the encounter note and then manually add in the other stuff. Waaay to time consuming. If I had a proper customized db, I could keep all the patient/healthcare provider/insurance information in the database and create an encounter form right in the db. With a drop down (customized for the type of patient, e.g., sleep, or pain, etc.), I could then enter in the data from either a sleep log or other type of paper self-report measure the patient has already filled out, into the encounter form. Then, if/when I need a report (either for the insurance company, or a summary for the referring physician), I could choose from a drop down the report I want to create, and merge the form data to fax to the doc, etc. I was thinking of having this DB on a server so if I chose to grow my practice, other providers could use the same system.

So, I'm not looking to build a complete (or even half complete) electronic health record (as much as I wish I had the time and knowledge to do so at present). I had considered Filemaker Pro, as it purportedly allows a user to build a DB quickly. But it's a closed system and if I did grow, expensive to scale up. However, your comment about time is money, is also something I should pay attention to as well. Maybe I should keep FMP on the table as a viable possibility.

Best to choose your final backend up front -- but like you mentioned, this involves a steeper learning-curve, quickly involving raw SQL. With PostgreSQL (MySQL, Firebird, Oracle, MS SQL Server, etc.) you'll also need to manage the backend separately on a daily basis (start/stop/backup tasks). But then, these are necessary tasks when running any database engine in server mode.

DACM, I really appreciate your thoughts. Thank you again. I've read a number of your posts scattered around OO/LO forums and have much appreciated your thorough but to the point responses.

I still wonder if what I'm trying to do is relatively easy with Base as the front-end and maybe with your thoughts above about beginning with the end in mind w/re to backend--particularly because what I'm trying to do is not create a full fledged EHR at the present time, but to ease my workflow and make is much faster to access pt data quickly. Maybe this too will take a lot of time to develop (sigh).

[georgesmith]

I agree with DACM and have privately told georgesmith that he should look for some shareware/freeware solution. A suitable solution ought allow him to interface to it for any specialised reports he needs. Designing and implementing a database is a non-trivial task (very non-trivial!); it is a fascinating intellectual exercise and we would not deny that, but it fragments the focus of any healthcare professional. My advice is that Georgesmith should find some system that does much of what he needs and get using that immediately; perhaps in his spare time over an extended period he may arrive at a bettter solution, but at least in the interim he would have a solution that handles a large percentage of his requirements. The important matter to determine is if the underlying database engine is externally accessible, rather than being in a proprietary format.

Sage words RoryOF. The frustrating thing for me is that I have not yet found a system that allows me to enter in the data I collect and spit it back out in an encounter form easily (not even close). Every commercial system I've found to date, while allowing me to track my encounters/progress notes, manage my billing, schedule appointments (and I won't be tracking appointments as much as I would like to. I recognize designing a calendar would likely be way over my head) forces me to be the proverbial square peg in a round hole: It ends up making my workday slower because the workflow doesn't work the way I work and I still end up having to do work-arounds with my data that is time consuming. I really don't want to design a database as much as I suspect if I had plenty of time, it might be a lot of fun. But the alternatives right now are still too time consuming; I spend way to much time on paperwork that a DB could likely streamline. But, I can almost hear DACM and you in my ear: the devil is in the details. Simple user friendly software takes extensive development behind the scenes. I appreciate everyone on this forums' candid and honest thoughts with a very healthy dose of reality. It's a bit discouraging (though I totally know that is not your intent), but I nonetheless appreciate it!!

Thank you so much RoryOF for all your time!!

[georgesmith]

Well again, no promises on painless. These two Java engines are closer to the SQL standards than any mainstream engine but that doesn't mean it's painless. For the tables and data, there are some commercial and open source migration tools available to aid in this task. But tables and data are only part of the equation. You may need to re-work data-types or re-create auto-incrementing fields, indices, etc. Even your front-end components (forms, reports, even queries) will often break when adopting a new engine due to things like syntax and schema support. Best to choose your final backend up front.

I had hoped by choosing an open-source and popular back-end (e.g., HSQLDB or Postgres) I might avoid the potential problems you mention above (see, really, I'm a newbie) and only have to re-work the front end. Oh well. Wishful thinking!! (laugh)

[ArthurBurke]

I would recommend you focusing on PostgreSQL, it has proven to be very intuitive and functional. If you require a detailed comparison you can read this [url=https://yalantis.com/blog/how-to-choose-a-database/]article[/url]. Good luck with your development.