Relationship Analysis

[Alexieoo]

Greetings,

I am attempting to create an animal hospital data base. I was wondering if anyone would be able to help me with my relationships before I start entering patient records and run into endless troubles down the road. Here are somethings you should know:

1) this was recreated for confidentiality reasons toward patient owners and therefore can NOT contain ANY owner information.



2) I have created a multivalued lookup field in tblpatients. I have been told these kinds of things can cause some trouble and I have also read some information on these troubles but couldn't quite wrap my head around it. The thing is, it's imperative that I have such a field because patients may undergo more than one procedure in a single day.

3) I understand that there are special characers and extremely long headers in the tables, but I will be conferring with my supervisor to break them down to much simpler ones.

4) I am operating on Windows 10 OS, and am running access 2007 not 2010 as it may show.

I look forward to anyone that can help me, thanks!

P.S. My Database is a smidge too big to upload here so if you must take a look at the file you can email me as well at: buchnyalex@gmail.com

[June7]

Your links for procedures and patients are suspect. Beware circular referencing. http://www.codeproject.com/Articles/...atabase-Design

What is purpose of tblRefProcedures? Why does it have PatientVisit? I don't think you need this link.

[orange]

Further to June's comments, in order for anyone to comment on your relationships it is critical that they have a description of your business rules. Your tables and relationships should support your business rules, but, since we have not seen the business rules, comments at best would be guesses.

[ssanfu]

And it would be better inf you didn't use multi-value fields or look-up fields in tables.

[Alexieoo]

Hey I have been told about that. The thing is, is its imperative that I have the lookup column. Would you be able to explain the issues and/or preventative actions?

And it would be better inf you didn't use multi-value fields or look-up fields in tables.

[June7]

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

Build multi-column combobox on form for the 'lookup'.

[Alexieoo]

Sorry I don't understand that page completely. I was wondering if you could summarize it in simpler terms to clarify what I am reading is right? Thanks

[June7]

Two main issues you will likely encounter:

1. viewing lookup field in table you see the lookup alias value, not what is actually in the field - I prefer to see actual value

2. because of item 1, since the field value is not what you see, say company name, if you include the table in a query and try to apply sort/filter in query design to this field using company name, it won't work because the actual value is company ID so a query like:

SELECT * FROM table WHERE [Company]="A Company";

will not work.

Many new to database development are often confused by use of lookups and how to deal with them when building queries and trying to filter or sort records.

[Alexieoo]

Okay thanks for making that much clearer. So my multivalued look-up column, formatted to combobox, using a table/query record source, is needed since there are multiple procedures and a single patient can go through many procedures in one day. How do I fix this ? Or what do you recommend

[orange]

I recommend you work through this tutorial. Then post your next question.

[Alexieoo]

I recommend you work through this tutorial. Then post your next question.

Hey I was wondering if I make a field primary key, given that it's a unique field, when I link that to the foreign key in another table will I be able to add the same data twice. For example: (Refer to screenshot as well)

tblProcedures.Procedure(PK) --> tblPatients.SurgicalProcedure(FK) where more than one patient could have the same procedure?

Question 2: I have created autonumber fields in each table to link that data as so:

tblPatients.PatientEntryNo(PK, this is to count each new record entry) --> tblPatientVisits.PatientEntryNo(FK)

tblPatientVisits.VisitID(PK) --> tblFollowup.VisitID(FK)

My question is, is it may seem like it would take additional effort to add each corresponding ID in the table. For example from tblPatients, PatientEntryNo:1, then have to go to tblPatientVisits, and put 1 there again. And finally for the last table have to put 1 two times for PatientID and VisitID in tblfollowup. Is there an easier way this can be handled? (Refer to the screenshot)

I was also wondering if anyone can check my new relationships

[June7]

You have autonumber ID fields in each table but not using all as primary key - why? For instance - tblAnimals - if you don't use BreedID as PK then why have the field (same for tblProcedures)? What is tblRefProcedures for?

Yes patients and procedures can be in tblPatientVisits more than once. Should not have both FKPatientEntryNo and PatientID in tblPatientVisits. PatientID should not be in tblFollowup.

Use form/subform arrangements for data entry/edit. The PK from main form will automatically be saved as FK in subform.

[Alexieoo]

Okay so for BreedID in tblAnimals I should just get rid of that? Seeing as it plays no use. In addition to PKProcedureID in tbl procedures? tblRefProcedures is where I will create the category code for each type of surgical procedure. Also if I create a subdata sheet in tblRefProcedures I can setup the subdata sheet to show me what procedures are under each category as there are multiple procedures in each category.

[June7]

Category? What is Category? Where is that? Show sample data of procedures.

As for the PK - your choice. Some would advocate the autonumber (because of indexing and linking). But if the descriptive text values are short (such as Male/Female) and no need to link to table to pull other info then I say just save the descriptive value. However, your situation is not that simplistic. What is NumberOfCases in tblAnimals? And surely procedures can have long descriptions.

[Alexieoo]

So if you refer to the screenshots I have provided examples of category codes for procedure types. The names of the procedures are no longer than 255 characters nor are they less than 30-40 at MOST. I have also attached a screenshot of tblAnimals, the number of cases represents how many times we have treated a specific breed of dog as you will see, will this pose any problems? It's only there so they can update the record, other than that I don't think it has a significant purpose. Also the subdata sheet is there and I can't recall putting it there, but it doesnt seem like a bad thing anyways.

I have also attached the updated design of my relationships as per your comment.