Hello:
I would like to get some assistance with reviewing the table structure (ERD) of a *sample* database. Please note that all values are notional and purposefully include only a few sample records for demo purposes.
BACKGROUND:
- I'm currently working on a research project. This research project includes multiple organizations (e.g., "ABC" and "XYZ").
- Each organization provides sample data (via MS-Excel flat file).
- The primary research effort incl. an assessment of their individual fields included in the Excel spreadsheets.
- Org "ABC" and "XYZ" (plus others) have a) distinct fields; b) overlapping fields (with same naming convention); c) overlapping fields that may be spelled differently (e.g., [LASTNAME] vs. [NAME_LAST]).
DATA MANAGEMENT GOAL:
a. Merge the individual spreadsheets and columns into ACCDB tables.
b. Normalize data in 3NF format (e.g., remove duplicate organization references in some but not all tables).
c. As part of the research and project interactions, organizations "ABC" and "XYZ" will provide different (maybe even conflicting) information for fields covering the same content.
d. So, while the normalization of same/alike fields will reduce the number of "net total distinct fields" (e.g., from 6 to 3), I must be positioned to track varying information/comments for up to 6 records (in this sample DB).
SUMMARY OF INITIAL SAMPLE DB STRUCTURE:
- T101_ORGANIZATION: in this sample DB, it only includes 2 records
- T102_MODULE: Each organization submits spreadsheets for n modules. This number will grow from, e.g., 2 modules to ~10 modules (for each org). More specifically, some orgs will have 10 modules while others may have 7 modules (or less / or more).
- T103_SOURCE_FIELD: As indicated in d., once field names have been merged, the number of distinct fields will be reduced (compared to the original # provided in their XLSX).
- T200_JUNCTION_OMS: Used as a junction table (PKs and FKs) in order to "tie together" {organization, module, source field).
- T301_RESEARCH: Per d., individual research is required for the lowest level of original field names (e.g., 6).
- T401_COMMAND_INFO: Similar to table T301, individual orgs will provide varying responses feedback for each field. The responses/assessment for, e.g., field [LASTNAME] may vary between orgs "ABC" and "XYZ".
- T501_ORGANIZATION_CONCURRENCE: Same as T401 except that concurrence (in support of research) should be tracked in a separate table.
My question:
- Upon review of the ERD, is there a *BETTER* (and more efficient) way of connecting the tables via PK and FKs?
- If so, I'd welcome for anyone to provide a sample DB (with or without records) that includes the modified ERD/structure.
Many thanks in advance,
Tom