Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Location
    Amsterdam The Netherlands
    Posts
    2

    Beginning db-design

    Question at beginners-level from a Delphi-programmer.
    I need to log people's medicine usage and
    now I'm stuck with my database-design.

    This is the situation :

    Table: User
    UserID
    Username
    ..

    Table: Day
    DayID
    DayMemo
    ..

    Table: MedicineDetail
    MedDetailID
    Dosage
    ..

    Table: Medicine
    MedID
    MedName
    MedUsage
    ..

    There is a many- to many relationship
    between Table.Day and Table.Medicine.
    (using Table.MedicineDetail)

    Table.Medicine is a list of medicine names
    and brands.
    Now, in this design there is only one list
    for all users, but I need a unique
    list of medicine for each and every user.

    How can I best do this?
    Thanks in advance.

    Mark.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Beginning db-design

    You want a many-to-many relationship between USER and MEDICINE tables. This is done by creating a third "intersection" table, perhaps named USER_MEDICINE, that has the structure:

    USER_MEDICINE( UserID, MedID, ... )
    PRIMARY KEY( UserID, MedID )
    FOREIGN KEY( UserID ) REFERENCES User
    FOREIGN KEY( MedID ) REFERENCES Medicine

    Other columns in this table (if any) would relate to the particular user and medicince, e.g. dosage, from_date, to_date, prescribed_by, etc.

Posting Permissions

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