[MadDoc]

Hi,

I'm a hobbist programmer and I have been coding with RealBasic for a couple of years now. I have a project in mind that is somewhat more ambitious than anything I have attempted before.

I am intending to write a patient management system for my father's GP practice. The trouble is, although I feel I am (reasonably) proficient in RB, I have never used any form of database software before.

I understand that (if I upgrade to the Pro version) RB can interface with many popular database softwares (such as various flavours of SQL, Oracle, Valentina, etc) and can be used to write a "front end" for the database.

My question is that the first port of call is obviously to build the database that will house my data (i.e. all the data about the patients in my dad's practice) but where on Earth do I start? Firstly, I do not know which database format to use (e.g. MySQL, postgreSQL, etc). Even if I had decided on the format, how do you actually build the database? Does the database building all have to be done in code? Is there no software that allows you to create tables, records and entries in a GUI format and then I can write the access to this database and add to it via RealBasic?

Sorry if these questions are really simple but I had never even heard of SQL until yesterday

Thanks in advance,

MadDoc,

[PBG4 User]

The first thing you need to do is figure out what you will be storing in the database. Next you need to figure out how all that data relates to each other. Then you need to figure out how to normalize this data so it can fit into a relational database.

You'll probably want to start searching on the Semantic Object Model (SOM) and Entity-Relationship (E-R) design. Then you'll want to search on normalization as you'll at the very least want to setup the database so your data is at least in the 3rd degree of normalization.

What you're looking to do sounds like a huge undertaking. Data integrity is paramount, since you'll be working with patient information. You wouldn't want info like the fact that a patient is diabetic to not show up on the screen then have your dad prescribe glucose tabs to them.

If you're serious about this, take a database class at a college. Already you sound like you're putting the cart before the horse. You need to spend a boatload of time analyzing the data you want to work with, how the data is used in the office and how it all relates. Then you need to build a design that can scale as you add more data over time. Then you need to take this design and then start looking at database servers to see which one would be most efficient for the job.

There's a reason medical practice software costs thousands of dollars.

[PBG4 User]

Some more thoughts, all the database 'formats' you list above aren't formats. They are different in how the data is managed. Any of those database programs work using relations. Sure, some give you higher-end functions like triggers, or customized programming environments like PL-SQL in Oracle, but underneath all the server funtionality you're still working with table and relational structures. You need to understand how all this works before you start slinging code.

If you hadn't heard of SQL until yesterday you have your work cutout for you. I would suggest building a few simpler databases and get familiar with data modeling before you even consider working with your father's data.

[Angus_D]

You might want to look at FileMaker or 4th Dimension.

[outsourced]

Read the book "Database Design for Mere Mortals."
I think the author's last name is Hernandez.

Also, don't forget about output. You'll need to either hand code the reports (no way!) or find a report generator that can read the data from your selected DBMS (MySQL, PostgreSQL, etc.)

I'm with Angus D: Look at FileMaker or 4th Dimension. You'll save yourself a lot of time and effort. Since you're only developing for your dad, you wouldn't need to be concerned about multiple-license costs.

[MadDoc]

Thanks for all your advice guys. Me thinks I may be out of my depth (at least at the moment). I'll check out the book outsourced.

MadDoc,

[Partisan01]

One word HIPAA. If you're in the US and you're dealing with medial records make sure you are in compliance with all federal regulations. If you are not you could end up in jail for failure to comply. Make sure you know what you're doing before you dive in, maybe talk to a lawyer or a compliance manager before you start so you know what you need to do.

With that out of the way choose a DB with stored procedures and views. I use MSSQL everyday, but for your project PostgreSQL sounds like it would work out fine, plus it has the features mentioned above. It also runs on OS X, and has a decent GUI. IMO the best GUI is TOAD for Oracle, but I'm biased, first love is hard to get over sometimes....