user treatment tests DB design help

[fife]

Hi Guys

 

I need some help with DB design and implementation of a feature my next website will have.  OK so I'm building a system that will warn the current user that the current client needs a certain test before they can have their next treatment.  Not all treatments required tests.

 

So far I have the following tables

 

user                  treatment                         treatmentTest                         treatmentUser        

iduser               idtreatment                        idtreatmentTest                        idtreatment

                        idtreatmentTest                  treatmentTestExpiry                 iduser

                                                                                                                     dateOfTreatment  (stored in yyyy-mm-dd)

                                                                  

 

(the treatmentTestExpiry stores how long until the treatment will expire   e.g   +1 month)

 

 

So every time a user comes in for a treatment we log the treatment in the treatmentuser table.  This works fine and I can see what treatments the user has had.  So certain treatments required a test to be done before the user can have that actual treatment. 

 

What I want is when you view the users profile page it says if they have not had a particular test or a previous test has expired and they will need another. I have no idea how to go about this.  Im just wondering if someone could help talk me through it?

[Barand]

 

 

warn the current user that the current client needs a certain test

 

It would appear from that quote that you want to know if the client requires the test, but you record the dates the user gave the test so how do you relate it to a particular client?.

 

treatmentTestExpiry -  is that a period in days?

[fife]

OK scratch that I now have some code to play with. I solved it by adding an extra table called usertest which stores all the tests the user has had and when they where performed.