If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Change of address
I am a self-taught novice (the worst kind!) so am hoping I can get a nice
simple answer to my question. First the history: I want to re-build a database I've been using for a couple of years. It records patients and test results for specific infections. One patient can have many tests at the same time which each have a unique number. Currently the patient name, DOB, GP, 1st line of address, along with test details - type of test, date of sample, reported date etc., are all in one table. This means if John Smith has 6 tests on one day I have to enter all of his personal details 6 times, so I am planning to pull that info out to a patient table. As time has gone on extra info has been added. Previously I had no unique identifier for a patient, so could never be sure if John Smith born on 01/01/1950 was the same as John Smith born on 01/11/1950 and it was a typo, or a different person, but we will soon be getting a national identifier which will help with data quality and confidentiality. So, on to my problem: If I have a separate table for patient details how should I deal with a patient moving home or changing doctor (GP)? It is possible that Mr Smith could be in a nursing home for a period of time then go back home, so I need to ensure tests are against the correct address (they could pick up an infection in the home). Should I add a new entry for him detailing the new address and then if he goes back home a 3rd entry which would be the same address as the 1st entry? How can I then make sure that tests are reported against the correct address when I do historical reporting? e.g.: address test ID date 23 Oak Lane 1234 01/04/08 Old folks NH 1254, 4578, 6541 10/06/08 23 Oak Lane 5547 23/09/08 Do I need to give each address a unique ID and pull that into the table containing test ID? |
Thread Tools | |
Display Modes | |
|
|