Can someone help out with this please?



This is the scenario. Patients are assigned a medical record number which is their unique identifier(sort of like a medical social security number).

On each visit they get a vist number. While on the visit the dcotor might order one test, no tests or many.

Additional fields on the table structure would be:
patient name

patient date of birth

date of first visit

date of a particular visit (this would link to their visit number-example if they came in 10/12/07 they would have a visit number assigned that day. If they came in again on 10/13/07 they would have a different visit number so that the tests ordered on 10/13 could be distinguised from the 10/12 ones etc.


I am having trouble picking the primary key and table setup. The medical record number is the unque number whereas the vist number changes each time they come in. Example-I could get a medical record number of 12345 that is unique to me. On my vist visit I might get V1 as the visit number and then V2 for the second and so on.

I need to see how the two tables would look (at least I think its 2).

Any help would be great.