Database Relationship Question

[Leonidsg]

Hello All,



I have a database relationship design question. Shown here: he "FirstVisit" table is the parent table of all my tables. Under it is a child table called "OrthoSurgery" table. There can be many OrthoSurgery records for only one FirstVisit record, hence the one-many relationship. Now to the question my other table in the picture is the OrthoBonesSurgery table which is a child table really of both: since A FirstVisit will have many OrthoBonesSurgery records, however OrthoSurgery will have one record with many OrthoBonesSurgery as well. The difference is there can be many individual OrthoSurgery records with multiple OrthoBonesSurgery records all relating to only FirstViist record. Another way to explain it is the firsvisit is a patient that visits the hospital. They are unique. They can have many unique surgeries with each surgery having multiple procedures. It seems to me like I should create a many to many relationship but I am not sure how best to set that up.

Please let me know if that does make sense.

Thank you as always.

Lenny

[recyan]

1) Unique FirstVisit for each Patient.
2) Each FirstVisit can have Zero or One or More OrthoSurgery.
3) Each OrthoSurgery can be associated with One and only One FirstVisit.
Now,
4) Each OrthoSurgery can have Zero or One or More OrthoBoneSurgery.
5) Each OrthoBoneSurgery is essentially associated with One and Only One OrthoSurgery.

If above 1), 2), 3), 4) & 5) are True, then, perhaps :

FirstVisit -> One-Many Relationship -> OrthoSurgery
OrthoSurgery -> One-Many Relationship -> OrthoBoneSurgery

Thanks

[Leonidsg]

This seems all correct, what is the best way to setup the one-many relationship between the Orthosurgery and OrthoboneSurgery. The unique numbers from them are MRN with Operation Date in Orthosurgery and MRN and Operation Date in OrthoBonesSurgery, but really I shouldn't have Operation Date twice, correct?

Not Sure thanks for the help.

1) Unique FirstVisit for each Patient.
2) Each FirstVisit can have Zero or One or More OrthoSurgery.
3) Each OrthoSurgery can be associated with One and only One FirstVisit.
Now,
4) Each OrthoSurgery can have Zero or One or More OrthoBoneSurgery.
5) Each OrthoBoneSurgery is essentially associated with One and Only One OrthoSurgery.

If above 1), 2), 3), 4) & 5) are True, then, perhaps :

FirstVisit -> One-Many Relationship -> OrthoSurgery
OrthoSurgery -> One-Many Relationship -> OrthoBoneSurgery

Thanks

[Leonidsg]

Here is what I setup, . Doesn't seam correct? I can't figure out if this relationship makes sense since it seems that one RecordID can't really be specific to a MRN. It think this may make more sense, not sure still. Also in order to make this relationship I need to change my MRN from Text to Number. Since it is indexed, should I change all my MRN fields in all my tables to be Number.

Thank you again for the help.

[recyan]

A few thoughts
1)
tblFirstVisit
MRN - PK - Autonumber
FirstName
LastName
.....

tblOrthoSurgery ( Assumption - MRN & ORDate together are unique for each OrthoSurgery Record i.e. for OrthoSurgeryID)
OrthoSurgeryID - PK - AutoNumber
MRN - FK
ORDate
...........

tblOrthoBoneSurgery
OrthoBoneSurgeryID - PK - Autonumber
OrthoSurgeryID - FK
OrthoBoneSurgeryDate
.................

2) I think you have a multi-value field in your OrthoBoneSurgery table. I don't have any experience with them, but have seen the experts advising against their use.

3) The last but the Most Important - Definitely, take a look at the links provided by orange in the below thread. I'm sure you'll find them very useful .
https://www.accessforums.net/database...ted-22393.html

Thanks

[Leonidsg]

First off thank you for the help. In the firstvisit table i cannot make MRN a Autonumber because it is a assigned Medical Record Number. Also in regards to the provided link, it does not seem to work. In regards to tblorthosurgery you are correct about your assumption that ORDate+MRN make a unique combination. I am a little confused on how you recommend to setup the relationship, if I create a MRN - MRN relationship I can then edit the relationship and add ORDate to is as well. If you don't mind can you ellaborate, what kind of field should OrthoSurgeryID be in OrthoBoneSurgery, how do I get it to autmatically fill, or would I need to input it everytime. Lastly, I am not to currently concerned about the multivalue field, they appear as strings, is there a particular reason they are a poor idea.

Thank you again.
Lenny

A few thoughts
1)
tblFirstVisit
MRN - PK - Autonumber
FirstName
LastName
.....

tblOrthoSurgery ( Assumption - MRN & ORDate together are unique for each OrthoSurgery Record i.e. for OrthoSurgeryID)
OrthoSurgeryID - PK - AutoNumber
MRN - FK
ORDate
...........

tblOrthoBoneSurgery
OrthoBoneSurgeryID - PK - Autonumber
OrthoSurgeryID - FK
OrthoBoneSurgeryDate
.................

2) I think you have a multi-value field in your OrthoBoneSurgery table. I don't have any experience with them, but have seen the experts advising against their use.

3) The last but the Most Important - Definitely, take a look at the links provided by orange in the below thread. I'm sure you'll find them very useful .
https://www.accessforums.net/database...ted-22393.html

Thanks

[Leonidsg]

I think I figured it out. Once I setup the relationship and created a form/subform then it autmatically inserts the data. Now my one question is what if I wanted to add MRN to the OrthoboneSurgery so I can have that table have a index as well. Is it okay to still add that field. Or if I want to do that should I just make two fields as the foreign key.

Thanks agian

[recyan]

1) It's OK, if you have the MRN as a unique field & have another auto-number field as Primary Key, which you can use in the relationships.
Pls take a look at below thread (Posts 12 & 13)
https://www.accessforums.net/databas...ign-32250.html

2) There is no need to again have MRN in OrthoBoneSurgery table.
I'll try to explain the way I think , OrthoSurgeryID, which is the primary key in OrthoSurgery table, itself reflects MRN & ORDate together. Hence by having OrthoSurgeryID as FK in OrthoBoneSurgery table, we have already included MRN & ORDate.

3) Sorry about the link not opening. Try below ( read the links posted by orange):
https://www.accessforums.net/databas...ted-22393.html

4) I might not be able to travel the path along with you for a couple of days. Hope some one comes across this thread & guides you along. Post your questions along with the latest relationship diagram to enable others to guide you.

Thanks