Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2007
    Posts
    2

    Table Normalization Help

    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.

  2. #2
    jya is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2007
    Location
    Chicagoland
    Posts
    109
    If I were to put something together, it might look like this...

    tblPatient
    Patient_ID - autonumber - primary key
    LastName - text
    FirstName - text
    DateofBirth - date
    .
    .
    .

    tblVisit
    Visit_ID - autonumber - primary key
    VisitDate - date
    Patient_ID - number
    .
    .
    .

    I would have at least two different tables, one for Patient information and one for Visit information. The two table would be linked to each other through the Patient_ID field.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Newb stuck on Normalization
    By dave_wilford in forum Database Design
    Replies: 6
    Last Post: 05-19-2008, 02:39 PM
  2. Normalization Assistance for Tables Please??
    By webmaniac in forum Database Design
    Replies: 10
    Last Post: 09-02-2006, 05:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums