Database design - advice please!
Happy New Year!
Following the success of the colorectal database I designed last year, I have been asked to create a database for another tumour site. I need a bit of advice on the structure.
This is what I think I need:
PATIENT (this is going to be the parent table)
Then I have 5 tumour sites for this speciality - RENAL, BLADDER, PROSTATE, TESTICULAR and RARE (what I thought was having 5 daughter tables using the _kp_PATIENT as the _kf for each table.
Then I need a SURGICAL TREATMENT and ONCOLOGY TREATMENT for each of the above - what I haven't done before is layouts / lookups based on a variable before. I guess there are 2 options - have separate SURGICAL TREATMENT and ONCOLOGY TREATMENT for each (which in some ways will be easier or have a single table. (PATIENT related to TUMOUR (x5) related to SURGICAL TREATMENT and ONCOLOGY TREATMENT x 5 or x 1). Any suggestions?
Of course, it isn't quite as simple as this as I will need to add in a few other tables - the main one being MDT MODULE this is for recording the minutes from the multidisciplinary team meetings and administration for the meeting This will need to be related to the tumour table.
As part of the function I will need it to create the list of the patients from each tumour site and sort them by each tumour site - I suspect this is going to be a sub-summary report? Again, not had the pleasure of doing these before. I will need a report that sorts all the patients that are on in each section of the MDT sorted by tumour site then by hospital.
Unfortunately, the timeframe for doing this is approximately 4 weeks - doing the tables, fields and lookups is going to be the quick bit - the layouts are the bit I find so time consuming to get right so that the data fields fit the processes involved for the people inputting the data.
Hope that is clear?