Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Posts
    5

    Unanswered: Need advice on tables/relationships...

    StudyID
    StudyName

    Site
    PhysicianLN

    AuditorID
    AuditorLN

    PatientID
    PatientInitials

    DeviationID
    Deviation

    The above fields are in a spreadsheet (there are additional fields, I dummied it down) and I need to create a database from it. How would you create the tables & relationships? I have the tables built, but I wanted to get some opinions. I will post what I have if interested, though I'm a little embarrassed by it (and it's not quite complete).

    General Rules & Info:
    There will be multiple studies
    There will be multiple sites per study – There can’t be two Site #2s in study ABC, however there can be a Site #2 in studies ABC & XYZ.
    There will be multiple auditors per site.
    There will be multiple patients per site – Like the sites above, can’t have the same ID in the same study/site but the ID could be used in multiple studies.
    There will be multiple deviations per Patient – Need to keep track of the site, patient, the deviation, and the auditor who entered the deviation in.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    looks good so far

    now you need some relationship tables

    SiteStudies
    Site
    StudyID

    SiteAuditors
    Site
    AuditorID

    SitePatients
    StudyID
    Site
    PatientID

    PatientDeviations
    PatientID
    DeviationID
    Site
    AuditorID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2004
    Posts
    5
    Thanks for replying! I follow the cross-reference tables you've listed, however what would the key field be in the sites table (for example)? Since there could be a Site 002 in study ABC and site 002 in XYZ, I can't use use Site. Don't I need to add the StudyID directly to the Site table vs. creating a cross-reference table?

    Thanks again!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the primary keys are underlined

    relationship tables typically have two columns, each of which is a foreign key, and which together form a composite primary key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2004
    Posts
    5
    Thanks, I didn't notice the underlines. However I still have a question about the sites table.

    SiteStudies above is a cross-reference between tblSites & tblStudies, correct? If so, what would the primary key in tblSites be? It can't just be Site b/c there could be two different 002s sites (with different Physicians)? I may not have made that clear enough. For example, Study ABC could have Site 002 with PhysicianLN="Smith" whereas Study XYZ could have Site 002 with PhysicianLN="Thomas" (two totally different sites). B/C of this, I need another field in the key to define the difference.

    I could create an autonumber field and use that as the key, but that wouldn't help with keeping duplicates out (entering Site 002, LN="Smith" twice for a study). I'm not a huge fan of autonumbers...

    Thanks r937!! You've been very helpful!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that is correct, you did not make it clear enough

    well, you seem to have grasped the essential concept

    just remember, you should do all relationships without using an autoincrement column

    of course, autoincrement columns might in fact be used, for example for the PatientID in the Patients table, but they should not (very strong emphasis) be used for relationship tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2004
    Posts
    5
    Sorry about that...there are so many rules with this puppy, it's really hard to explain in writing.

    Thanks again for the help!

Posting Permissions

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