table design trouble

[nivashni]

Hi guys

I am having some trouble designing my tables correctly based on the relationships that I need to maintain.......

I have at the moment the following tables:-

product
prod_id(PK)
prod_name

nutrient
nut_id(PK)
nut_name

medication
medication_id(PK)
medication_name


The relationships is as follows...

a medication (eg viagra) can deplete many nutrients (eg Vit A Vit B, Vit B12 etc etc ).

each nutrient that is delpleted can be assited by one or many products because of the depletion (products eg: Calc vita, mega b & c , seaweed etc etc etc )

That would mean 2 more tables, medication-nutrient(1:M) and nutrient-product(1:M)


Now the flow of information is as follows..I ask the visitor if he is taking any medication from a list that I provide via checkbxes,he may select 0,1 or many medications.

That will then check what nutrients are depleted by these medications and then recommend products...

My problem: I need to save this so-called assessment in another table so that when the visitor checks at a later stage I can give him the same report.

My challenge:-

At any time the admin may decide that a certain medication is no longer depleting a nutrient, or a product is no longer helping with a depletion etc etc
but the intial report that was given to the visitor must be available..

So if the admin decides that viagra no longer depletes vita B, he will make the necessary to the other tables,but the table that stored the assessment
must still show that depletion, so if the client was taking viagr ,basically we will not tamper with the information that we initially presented to the visitor..

Any thoughts on how I manage this problem...

Please help guys
Niva

[nivashni]

Hi Guys

Anyone had a similiar problem before....

Niva

[Tangerine_Dream]

At any time the admin may decide that a certain medication is no longer depleting a nutrient, or a product is no longer helping with a depletion etc etc
but the intial report that was given to the visitor must be available..

So if the admin decides that viagra no longer depletes vita B, he will make the necessary to the other tables,but the table that stored the assessment
must still show that depletion, so if the client was taking viagr ,basically we will not tamper with the information that we initially presented to the visitor..

Hi, IMHO you should use the following tables:

'medication_assessment' table:

patient_id (PK) (FK)
medication_id (PK) (FK)
nutrient_id (PK) (FK)

describes what Medication was given to the Patient to deplete what Nutrients

'nutrient_assessment' table:

patient_id (PK) (FK)
nutrient_id (PK) (FK)
product_id (PK) (FK)

describes what Nutrient was given to the Patient in what Products