3 Replies Latest reply on Jan 12, 2010 12:40 PM by philmodjunk

    Database design - advice please!



      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?


      Best wishes  



        • 1. Re: Database design - advice please!

          Reports will be easier to set up if you use one table for all your tumor sites. Perhaps you could simply add a field for "site" that stores "RENAL, BLADDER, PROSTATE, TESTICULAR or RARE" as a way for finds and/or relationships to distinguish between records for each such site.


          Then you can more easily design reports based on this table that report information for any combination of "tumor site" records you need.

          • 2. Re: Database design - advice please!

            Thanks Phil,


            I guess I am going to have to learn how to do lookups differently to be able to do this eg operation - otherwise the drop down will be huge ;-( and the button will need to choose which layout to go to for tumour and treatment from the portal.



            • 3. Re: Database design - advice please!

              Check out conditional value lists as a way to prune value lists down and make them shorter.


              A filtered portal that limits visible records by selected tumor site might also be useful.


              You may find you can use one layout for all tumor sites but just set up a script that finds only records of a specified site designation before switching to the desired layout.


              Here's a thread on setting up conditional value lists:

              Custom Value List?