SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2005
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    table design trouble

    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

  2. #2
    SitePoint Evangelist
    Join Date
    Mar 2005
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Guys

    Anyone had a similiar problem before....

    Niva

  3. #3
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    Moscow, Russia
    Posts
    557
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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:
    Code:
    patient_id (PK) (FK)
    nutrient_id (PK) (FK)
    product_id (PK) (FK)
    describes what Nutrient was given to the Patient in what Products


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •