Relationship Analysis

[June7]

NumberOfCases is an aggregate value. This should be calculated when needed, not saved into table.



I would not use a text field that can have up to 255 characters as a PK.

tblRefProcedures has only the number field - no description of what this is? Still don't understand this table. What would data in tblProcedures be like?

[Alexieoo]

NumberOfCases is an aggregate value. This should be calculated when needed, not saved into table.

I would not use a text field that can have up to 255 characters as a PK.

tblRefProcedures has only the number field - no description of what this is? Still don't understand this table. What would data in tblProcedures be like?

I have adjusted my text field sizes for my PK's. tblrefprocedures is would be used to categorize each type of surgical procedure performed on any given patient. For example a "Splenectomy" is removal of the spleen and is categorized as an Abdominal procedure. As you can see the table would be set up as ProcedureName, and then ProcedureCategoryCode. I was wondering if I should create an autonumber field called ProcedureID as the PK instead of a text field called ProcedureName as the PK?

[Alexieoo]

My apologies for my delayed response I got side tracked with other duties

[June7]

Sorry, still don't understand the procedure tables.

Why do you have SurgicalProcedure in tblPatients - can't patient have more than one surgical procedure? Isn't this what tblPatientVisits accommodates?

Possibly tblProcedures should be linked to tblPatientVisits.

Consider:

tblAnimals
Breed

tblPatients
PKPatientEntryNo
DOB
DOD

tblCategories
Category (Abdominal, Thorax, Minimally Invasive, Other)

tblProcedures
PKProcedureID
ProcedureDesc (e.g Splenectomy)
ProcedureCategory (e.g Abdominal)

tblPatientContact (visit or phone)
ContactID
FKPatientEntryNo
ContactDate

tblContactDetails (instead of tblFollowup - a followup is a visit or maybe just a phone contact? - allows multiple detail records for each visit)
FKContactID
Reason (initial, followup, surgery)
FKProcedureID
Notes
other fields

[Alexieoo]

So it would be Figure A(yourdesign) vs Figure B Your design seems sensible because to have the exact same field in two different tables is just repetitive... And you will also see that I left Number of cases in tAnimal. What kind of significant effect could it have the database design? Because wouldn't they just be able to update it on their own by simply manually increasing the value of the numbers?

[June7]

Still not quite how I would do the PK/FK for tblProcedures.

Yes, can store the NumberOfCases in table but depending on users to do accurate count of visits by breeds is risky. I would not do this. I would build queries do the count when need. Most reliable and accurate as it is based on raw data. The stored value is disconnected and cannot be verified without doing the query anyway as a check on the data.

[Alexieoo]

Oh Sorry I seemed to have missed the tblprocedure edits, which I also agree with, thank you. As for the contact tables can't I just save the hastle of creating new tables and relationships and add for example "Additional Visit Notes" in tPatientVisits, and the followup is primarily for the Clinicians at the hospital more than the owners. But incase they want to record the follow up I can again put "Additional Follow Up Notes".

I have a couple of questions a little off tandem but just to remind myself:

1) if you look at my tables, for example lets use tblfollowup, you can see that there are some fields that require one option or the other (excluding yes/no datatypes). I haven't even heard half of the troubles, but I feel like these coulmns really need it. I want to make the database as userfriendly as possible.

2) Should all my foreign keys have the "indexed" box checked under the "Fields" group in the "Table Tools" tab?

3) Would it be useful that instead of the Number of cases coulmn I create a query named qryBreedCountByProcedure and then have the criteria request the breed in question that will then pull up the breeds and other relevant info?

[June7]

Sure, if that works for you and the business requirements.

1. If every time you learn about a new 'trouble' means creating a new field then the design is not optimized and certainly is not normalized. There is a limit of 255 fields in a table. A normalized db seldom needs a fraction of that in one table.

2. Yes, and an autonumber field will do this automatically. Index (No duplicates).

3. Yes, basically what I have been saying.

[Alexieoo]

Sure, if that works for you and the business requirements.

1. If every time you learn about a new 'trouble' means creating a new field then the design is not optimized and certainly is not normalized. There is a limit of 255 fields in a table. A normalized db seldom needs a fraction of that in one table.

2. Yes, and an autonumber field will do this automatically. Index (No duplicates).

3. Yes, basically what I have been saying.

Sorry I didn't word question 1 remotely well at all... What I meant is some columns in each table may require the user to pick 1 of 3 or maybe 4 set options. For example tblFollowup you will see one that says "died or euthanized" I want to be able to a list box (or in otherwords a lookup) for the two options so that the user doesn't need to type in the complete word. Maybe I don't understand the troubles of lookup fields completely but would this create a hassle in my database if created such a coulmn?

Also for question three, sorry I don't mean to take credit for your idea, I am not too literate with computer language all the time.

[Alexieoo]

Also in regards to question two, I would have to allow duplicates for a field such as breed in tblPatients and Surgical Procedures in tblPatientVisits correct?

[June7]

I don't build lookups in tables. Build combobox on form.

Certainly multiple breeds in tbPatients and procedures in tblPatientVisits.

Don't really need species in tblPatients. This should be in tblAnimals.

[Alexieoo]

I don't build lookups in tables. Build combobox on form.

Certainly multiple breeds in tbPatients and procedures in tblPatientVisits.

Don't really need species in tblPatients. This should be in tblAnimals.

Okay will do. So for my procedures in tblPatientVisits: I will import the data into the table . And then in the frmPatientVisits I would like to create a multi valued look-up since one patient can go through more than one procedure in a single day. Is this fine?

[June7]

I don't use multi-value fields. Before you decide to use, better thoroughly understand what they are. https://support.office.com/en-us/art...C-6DE9BEBBEC31

[Alexieoo]

I don't use multi-value fields. Before you decide to use, better thoroughly understand what they are. https://support.office.com/en-us/art...C-6DE9BEBBEC31

Took a look at the article. I see they said it would establish a many-to-many relationship which I heard is not very common. Also that I need a small list, my list is approximately 55 choices long. Do you have a proposition as to how I can assign many procedures to a single patient in one record? Or will I have to duplicate the record and enter a different procedure? Therefore this will change the uniqueness of some of my PKs.

[June7]

many-to-many relationship is actually very common in relational database. You certainly have in your db: patients can have many procedures and each procedure can be associated with many patients - this is many-to-many. Many-to-many requires junction table.

Do what Access is doing 'behind the scenes' with the multi-value field - build a related dependent table (junction table that relates patients with procedures). Or use the multi-value field. The choice is yours. Just wanted to make sure you were knowledgeable first.