Need assistance with ERD/table structure

[skydivetom]

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

[June7]

Is this to document field mapping for each organization workbook?

Still developing tables that would receive normalized data?

Perhaps provide sample of workbook.

Suggest not using all UPPER CASE in naming convention - this is harder to read and don't really need to scream the names.

[madpiet]

Without the questions the database is supposed to answer, it's harder to tell.

[skydivetom]

@June:

Thank you for the response. See info below:

1. Multiple spreadsheets (single worksheet only)... there's a chance that future orgs may have multiple worksheets though.
2. The table design is a function of the process. The process is focused on the assessment of the fields themselves. For right now, all records can be ignored.
3. Attached are 2 notional sample spreadsheets with additional fields. All of the additional fields would be added to T103. All other tables (in previously provided ACCDB) are, again, in facilitation of the process itself.
4. Copy on the UPPER CAPS... that can be easily changed.


@madpiet:
Thank you for the response. I hear you but allow me to defer to the "background" and process description. Ultimately, the DB is to assist the research effort w/ the understanding that organization "ABC" may indicate "I don't need all fields in the new system" while organization "XYZ" says: I must have every field in the new system. I understand that's a bit vague but honestly, that's all it comes down to.

[skydivetom]

So, per post #4's *notional* sample data, the modified value set for T103 would be as illustrated below.

[madpiet]

I think you can do this in PowerQuery. I'm pretty sure Chandeep Chhabra covered it in one of his videos on youtube. basically, you have a translation to map the various column names to a standardized column name, and then once you have that, you can just append to a single table and the data maps. Super fun, right?

[skydivetom]

It is preferred to stick to MS-Access.

If you have a specific recommendation on the ERD, I'd welcome your feedback.

[madpiet]

"do this" meaning mapping the columns.

[skydivetom]

Yes, that's correct.

[madpiet]

FWIW, here's Chandeep's video: Combine Data from Multiple Excel Files with Inconsistent Column Names - YouTube
Then once the data is in Excel and cleaned up, just import from there. It's a whole 18:30, so might be worth watching.
Otherwise, you'd need to loop over the Fields collection of the table and find the matching field name from a table of "translations" (DestinationColumnName, SourceColumnName) Just sounds like a huge pain. But you do you. Do it in VBA if you want.

[skydivetom]

Madpiet -- it's *not* about the different field names with alike content.

The underlying ERD question is about tracking admin fields such as "Research Status" and/or concurrence, etc.

I'd like to ensure the proposed ERD facilitates a valid relational DB structure for fields above and beyond those listed in sample XLS files.

Hopes this makes sense now.

[madpiet]

I'd like to ensure the proposed ERD facilitates a valid relational DB structure for fields above and beyond those listed in sample XLS files

In order to make sure my structures are valid, I generally apply the rules for the first 4 normal forms (1,2,3, BCNF). If I get to Boyce-Codd Normal Form (3NF+), then I'm safe. The other way to check it is some sample data and do some CRUD and see if anything breaks. (So you'd use a minimum viable number of records per table (like 3) and test.) But I've been doing it long enough, so that I'm pretty sure I can take a list of questions the database has to answer and test against those first. I just point out the tables and columns required to answer a question. YMMV

[skydivetom]

Ok. Appreciate it.