Unanswered: Need advice on tables/relationships...
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 cant 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, cant 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.
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, 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...