Double Check relationships?

[Alexieo]

Greetings,

I am attempting to start up my first database for a hospital and I was wondering if someone could take a look at my relationships/tables and ensure that I am on the right track. If not please provide feedback!

Also, I have a lookup column in tblPatientLogInfo under "Surgical Procedures", since it has so many options I was wondering if there was a way that I can just type part of the name in and get the closest options to filter out? It is imperative that it's set up this way because a single patient may undergo multiple procedures in a single day. Although I heard this column type may bring a lot of stress in the future I was wondering if I can get a little input on that as well.

I see this as a very big yet kind deed for someone to do, so thank you so much for your time if anyone decides to help.

 

[MarkK]

Hello and welcome to the forum!

It doesn't make sense to me to have your patient and patientloginfo tables related 1:1 on three different fields. I think at this level if you have tables related 1:1, you should just put all that data in one table.

Apart from that, a table should describe a particular type of thing, so good names for tables are: Patient, Animal, Appointment, SurgicalProcedure.

What does OVC mean at the end of your table names? Why bother with that?

Pick shorter field names than: "Patient staged by Onco prior to Sx Transfer? Y/N" and don't use spaces or special characters like ? and /. This field might better be called "StagedByOnco"

I wouldn't make my primary keys have two fields in them. Just use the autonumber field.

Most importantly though, the table that records procedures you actually perform is missing. You have a comprehensive list of procedures and you have patients, but now you need a new table that records the PatientID, the ProcedureID, and the date/time, and stuff like that. The table you have is like a catalog of procedures, right? But when you perform one of those procedures on a patient, in a room, by a doctor, at a particular date/time, with some measureable outcome, that is what you might call a "PatientProcedure." And if you perform two of those on a single patient in a single day, then you add two records to that table, with the PatientID, ProcedureID, DoctorID, DateTime. That table is absent from your design.

Hope this helps,

 

[Alexieo]

Greetings Mark,

Thank you so much for your prompt reply, much appreciated. You have great advice. I cleared up those three tables with 1:1 relationships, I must have clicked something by accident (I think it was "Show Direct" in the rightclick drop menu). And I will be working on those headings and table names for more clarity to the user.

I do have a couple of questions for your advice though:

1) I put two primary keys in the table because one is for the single record entry and the other is for the patient ID in the hospital and therefore I need atleast the patient ID which can't be an autonumber data type. Also doctors may want to count how many times a single procedure was performed on any given day, month, or year (which I will be creating qry's for) which is why I kept the autonumber field there so they can easily count it. Thoughts?

2) Is there away I can format the lookup I have (refer to screenshots) so that I can partially type the procedure name in as opposed trying to eye it out, it may become eye boggling once more procedures are added.

 

[MarkK]

You have not addressed my main point, that you are missing a table. A patient might have many S.Procedures and a S.Procedure might be performed on many Patients, so that is a Many-To-Many relationship. Your SurgicalProcedure is like a catalog, but you need to add the table to record when the surgery actually occurs and who the patient is. This table is required, but it is missing from your database . . .

tSurgery
SurgeryID (PK)
SurgicalProcedureID (FK)
PatientID (FK)
DateTime

. . . and once you add it, it will solve most of the problems you are talking about.
See what I mean?

 

[Alexieo]

Thank you so much for your help. I created tblSurgeries with the designated keys and I feel much more at ease with the relationships and the design.

I'll be working on inputting the records for now and then start to create the qrys, frms, and rpts.

Thanks again! If you would like to take a look at the updates I have attached the file. I have added new stuff and realize some of the headings are quite long but I will have to refer to my supervisor to shorten them to her liking.

 

[llkhoutx]

Your tables and relationships, in my humble option, need to be given more thought. Your naming convention is laborious. Google "Hungarian naming convention" for a commonly used scheme for naming fields, objects and controls; use will save a world of coding and immediately identify the data type of each field, object or control. Otherwise, one will not remember a data type by sight in complex code, tomorrow, next week, let alone next year. This is very important, but not immediately evident to neophytes.

I have attached a new database to this comment of simple tables using your basic scheme, but with simpler meaningful pneunomic table and field names.

Note particularily each relationship and the cascading update and delete checkboxes for some, but not all relationships. Deleting an Owner will also delete all his "Patients" (pets, I assume), and all the owners/pet "Visits." Welcome to the purpose of table relationships.

Another note, never ever use spaces or special characters in object, field or control name. This almost obviates the use of brackets of the names of fields and controls in queries or complex expressions. Use of brackets, which I abhor, causes unnecessary complexities in coding.

Modify or use what I have provided, in whole or in part, if you deem any part of it useful. Good luck.

 

[MarkK]

Yeah, your tables aren't done. You don't need a PatientLog table AND a PatientOwner table. They describe the same thing, so they will have a 1:1 relationship, so merge them. You have not uniquely identified the animal. What if one owner has two dogs? Your system cannot handle this case.

You need Patient, PatientAnimal, Animal (which you currently call DogBreed)

Animal table should look like . . .

tAnimal
'this is the type of creature - abstract - like a catalog - rarely changes
AnimalID
Species (Dog, Cat, Snake, Cow)
Breed

And then an animal as owned by a patient . . .

tPatientAnimal
'this is the particular creature - concrete, as owned by a person - new record for every new animal you see
PatientAnimalID
PatientID
AnimalID
Name
DateOfBirth

This way your patient can own many pets. And now you have to change tSurgery, because your surgeries occur on the pets not the patients, so you need to link the surgery to that PatientAnimalID, not the PatientID.

This is the normal process of table design.

 

[Alexieo]

Hey guys,

Thanks a lot for the help, I'll keep you posted

 

[Alexieo]

Hey guys,

So I was notified by my supervisor that the reason we are creating this database is because of confidentiality reasons for the client, of course.

I tried my best to recreate the tables and relationships by collaborating each of your ideas. If you would like, please let me know what you think?

 

[AccessBlaster]

On the second screen shot, there is at least one table level look up. Which could be trouble down the road.

 

[Alexieo]

I have also heard but am not sure why. Can you tell me?

 

[AccessBlaster]

This is a good explanation.

http://access.mvps.org/access/lookupfields.htm

 

[AccessBlaster]

Do yourself a favor book mark these two sites.

http://allenbrowne.com/tips.html
http://access.mvps.org/access/

 

[Alexieo]

I have been working on them and have read tutorials and tried again I was wondering if someone good give me a critique? Much appreciated

 

[MarkK]

What about the critiques you have already received? You are missing a table. Let's say a patient drops off a dog called "Poochie." Where do you put that name? Where do you put that doggy date of birth? That whole table is AWOL from your database.

 

[Alexieo]

What about the critiques you have already received? You are missing a table. Let's say a patient drops off a dog called "Poochie." Where do you put that name? Where do you put that doggy date of birth? That whole table is AWOL from your database.

I have a field in tblPatients for the Patient Name, I will find out if they want it in every table suitable since they usually just go by PatientID. As for the DOB thank you for reminding me I just added it in.

 

[MarkK]

So is the patient a human or an animal? Where is the contact data for the owner? Maybe I've misunderstood your structure here.

 

[Alexieo]

So is the patient a human or an animal? Where is the contact data for the owner? Maybe I've misunderstood your structure here.

The patient is an Animal, sorry if I failed to make that clear Mark. And the contact data for the owner must be excluded from this database they already have a database with that info.

 

[Alexieo]

So here is an updated version of the database. I have fixed my relationships and created forms. I heard that it is better(maybe easier?) to do data entry/edits in forms? Any opinion on this?