Introduction *updated*

The focus at this very point in time is to develop a rational and hopefully logical database design. I have drafted a rough sketch of the ERD and am currently working on a rough mockup using FileMaker Pro 11.

This problem requires me to do more work on my part in providing a better representation of the specific issues I'm having, so I will be posting another ERD later tonight along with developing some actual questions as Blindman pointed out.

I'm not asking anyone to do this for me, as my boss has tasked me to do it but I am definitely looking for guidance as to how I can solve this problem. I'm going to go over what I know for the project requirements, what my proposed ERD is, and hopefully I can get some feedback as to what direction I should take it.

I'm feeling like my design is going to end up looking like this and I'm not going to understand it enough to build it.

Hindrances

• There is no structure to follow, IE: there is no set procedure for tracking or checking appointments

• I have been given free reign to design the entire project. (Isn't that scary enough?)

• Procedures for tracking, making and checking appointments and everything else have no expectations (that have been voiced currently or will be in the visible future)

• I have rather limited (read no) experience in creating ERDs let alone creating databases, but as there has been no allocated money towards this project and I'm still in need of a job I must take this job on.

I would greatly appreciate the help and I am treating this as an interesting but challenging logic problem!

Requirements for Database (Presumed by me):

• The database must track scheduled appointments for patients, along with missed and/or rescheduled appointments

• Each patient will have a history of treatments and/or medications prescribed and the doctor(s) they saw

• Treatments will have specific costs associated with them, and some treatments will require follow up appointments to be made (possibly denoted by the treatment. IE: Treatment A requires the patient to make 2 follow-up visits once a week for the next two weeks)

• The Database must track both Staff (such as secretary/receptionists) and Physician (Doctor, Chiropractor, etc) hours and the locations that they worked(Multiple offices and possibly some house-calls made)

• The medication stored in the office(s) needs to be tracked and accounted for, incoming shipments and payments due need to be tracked also

• Needs to track successful billing and when account receivables are sold off and to whom

*NOTE* This is still a working list, and has in no way been completely finished or even validated as true in regards to some of the statements. Your help in theses decisions would be much appreciated */NOTE*

Required Entities:

• Hours (DayEndTime, DayStartTime, EmployeeID, OfficeLocation)

• Physician (Designation, InsuranceNumber, License, Specialty)

• Staff (CellPhone, EmployeeID, FirstName, HomePhone, LastName, OfficeExtension, OfficeLocation, OfficePhone)

• Office Location (AddressCity, AddressState, AddressStreet, AddressZip, AlternativeContact, BusinessEmail, BusinessFax, BusinessPhone, PrimaryContact)

• Patient (AddressCity, AddressState, AddressStreet, AddressZip, CellPhone, DateCreated, DateModified, DOB, EmployerName, HomePhone, PatientFirstName, PatientLastName, PhysicanPreference, SSN, WorkPhone)

• Medical History (Allergy, Height, IllnessHistory, Medications, Symptoms, Weight)

• Car Insurance (AccidentType, Attorney, ClaimNumber, CollisionType, DateOfInjury, InsuranceCompany, PolicyNumber, SeatPosition)

• Patient Medication (EvaluationDate, PrescribedDate, RefillDate)

• Medication (DoNotCombineWith, DosageInstruction, Dose, Name, Strength)

• Medication Stock (Amount, MedicationID, OrderDate)

• Appointment (AmountBilled, CreatedBy, DateStartTime, EmployeeID, EndTime, PatientID, ReasonForVisit)

• Services (Cost, Description, FollowUpSchedule, Name)

• Referral (ReferPatientHow, ReferPatientWhen, ReferPatientWhere, ReferPatientWho, ReferPatientWhy)

*NOTE* This is still a working list, and has in no way been completely finished or even validated as true in regards to some of the statements. Your help in theses decisions would be much appreciated */NOTE*

*2nd NOTE* I finished my first draft (very very rough) of my ERD and have attached it, I am updating the requisit fields as we speak but I have opted to leave out the PK/FK elements at this very moment. If I have missed any obvious/implied fields I would appreciate your opinion! */2nd NOTE*

I want to address everyone who might read this, I got over 40 views from when I last checked and not a single response. I know this isn't an easily solved problem, so what I'm going to do is make "draft" databases and revise my design as I proceed. Since this is mostly theoretical I believe I'll have better luck working with a mockup so I'll see the interaction with the data.

I'll continue to update these two posts with more information, as I'm positive I will run out of room on the first post.

I suspect most people read your post and quietly started shaking their heads. Have you tried explaining to your boss that you don't have the skills required to do this? Have you pointed out the gaps to him? Or were you just hoping we'd do everything?

Sadly if you're going to write the software and create a database then you'll need some ability on these fronts. It's not rocket science but I just can't see what you're bringing to the table.

I think your best hope is to concentrate on the full requirements and business model. Then just look for some standard software that fulfils the majority of these requirements. You can later adapt it to better suit the surgeries needs. Have you tried ringing other surgeries and asking what they use?

I don't see where you actually asked a question...

Sometimes no response is, actually, a response... How much time do you think we professionally employed Database Developers have to devote to figuring out what your issues are? Ask a question, directly and succinctly, and you will get a response. Now I have to get back to the job that pays me....

I appreciate the reply, as I said in my first post I understand the issue with the lack of skill on my part, the lack of information given by my boss but unfortunately I've talked in length with my boss and he's convinced of the decision he has made. I'm using a software program to build this database, so the programming side isn't the issue... I'm not going to be teaching myself programming while also teaching myself Databases. I realize that this seems to be another "help me with everything right now, do my work for me" thread but all I'm asking for is mild guidance and some outside perspective. I've been reading database books, watching online videos (lynda.com) and exploring a lot of online examples.

The reality of what I'm doing is reaching out to capable people to which I could pose questions and actually have them understand. Not a single person in my company understands Database design so I have no way to get a 2nd opinion or even a "your wrong". I tried to get a decently descriptive overview of the problems I'm having, the steps I've taken and where I'm at and if this thread is completely uninteresting to the database community as a whole I'll delete it.

*Edit* @Blindman I again, appreciate your response and I understand the validity of your concerns. Your reply is rather valuable as you pointed out the very important fact that I didn't ask a question. Thanks for that, and I will be making edits to my original post(s)

If you completely change your original post then it simply makes the following posts nonsensical. Also no-one will know that you've changed anything as no posts are sent out to subscribers regards this. Better to just list your questions in a new reply or, better still, come up with an attempt at the database and then people can comment on that.

Very Zen

Hi

>>>*Edit* @Blindman I again, appreciate your response and I understand the validity of your concerns. Your reply is rather valuable as you pointed out the very important fact that I didn't ask a question. Thanks for that, and I will be making edits to my original post(s)[/QUOTE]

I have a page on my Database Answers Web Site that lists over 880 Data Models.
Some of them will give you some insights, like this one on Patient Care :-
Patient Care Data Model

and this one on Patient Tests :-
Data Model for Patient Tests


and Doctor's Practices :-
Doctors Practice


HTH

Barry Williams
London, England

SabakuUsagi,
State a new post. One question per post. Be as specific in your question as possible, and supply any relevant database diagrams or table definitions.

Thanks for that suggestion Barry, I do appreciate the starting point. I took the basic concept of this and tried to expand it to suit my needs, I'm still trying to understand the actual relationships between tables and how they interact with each other. IE: the billing information relative to the patient's treatment and medication history.

I'm using FileMaker Pro and I have yet to turn this into a mockup database, I'm planning on doing more work tomorrow in FileMaker to test this. I do apologize if the ERD itself is flawed (flow of connections) mainly due to the latest program I'm playing with. "DBDesigner 4" If you have any pointers for that specifically I'm open to those as well.

Questions:

1. Is the ERD I've mocked up a model that will work?
2. Am I using too many entities to show the relationships between the main components? (Staff, Billing, Inventory, Hours, Patients, Treatments, Medication, etc.)
3. Do you have any suggestions/revisions for this ERD to help achieve my end goal?