Confused about junction table implementation

Question

First things first, I'm not a programmer and this is the first time I've worked with database creation and this implementation is part of a need that was identified during the internship that I'm currently doing.

I'm currently developing an MS Access database that will be used to manage work contracts with sports trainers. The implementation aims to migrate a series of data that's always been stored on flat Excel databases.

Right now I'm having an issue with how to implement a functionality that is needed in order to do some searches.

Table A has about 200 trainers, each is identified with their own ID number and their name, which is used to manage the relationships with other tables (things like social security, phone numbers, adressess, etc). ID is the key. It's structured with three fields as the example shows.

[Trainer_ID] [Name]  [Last_Name]
1            Pedro   Pérez
2            María   Gómez
3            Hollman Vivas

Table B is a list of the (currently 20) sports we handle contracts for. It has an autonumber field as the key and the name of every sport as short text (using lowercase characters for example only). The list can be updated with more sports depending on demand and trainer enrolment.

[Sport_ID] [Sport_Name]
a          Soccer
b          Basketball
c          Tennis

Finally, Table C stores the specific certifications every trainer has, with the Trainer ID as the key. In the Excel version it has four fields.

[Trainer_ID] [Sport1] [Sport2]   [Sport3] [Sport4)
1            Soccer
2            Tennis   Soccer
3            Tennis   Basketball Soccer

All the data in Table C is scrambled like that.

As you can see, every trainer has one or more certifications that drive to almost endless combinations (like 1abc 2c 3ac). Not to mention, eventually we can have a trainer with five or more certifications and the database isn't designed with that in mind.

I need to find a way to make sense of that data in the environment of the MS Access Database but can't really think of the best way to do it, considering that due to the sheer amount of information that we handle, we need that the data can be easily updated through mass uploads from .csv files but also updateable through the forms that are used for manual verifications.

It needs to be used in queries to search for specific competences when required.

The first thing I tried was leaving the data as it was (with the four sports fields) but it proved to be a mess to handle.

Then multivalued fields, which were scraped after 10 minutes because my research on the subject shows it's a non standard implementation and can't be updated through mass uploads to the base.

Further research drove me to junction tables, but I still haven't figured out how to structure the thing considering most examples on the web are based on two tables, not three.

I was thinking about having the certifications table (Table C) without key fields and just store fragmented information with repeated use of [Trainer_ID], but I'm afraid this lack of normalization can lead to problems in the future.

As I described before, the database should be able to store, display and update the qualifications of every trainer in a consistent way no matter how many certifications they have.

Answer 1

There is nothing wrong with your idea of using the Certifications table with repeating Trainer ID's. It isn't redundant data because you would have a Certification_ID in the table as well, to act as the primary key. Your two other fields would be foreign keys from the two other table.

Intermediary(Junction-tables) tables are VERY common in large scale databases that run Multi-Billion dollar corporations, so there should be no worry.