Database design and a "Joiner" (Many-to-Many) Table

[Lord_Vader]

I have a many-to-many table (I'll call it a Joiner table, is there a real name?) that has the fields:

ID_Note, ID_Unit, ID_Calc, ID_Widget Type.

ID_Note is the "main" field, I have a form that is bound to the ID_Notes and a subform where you choose your ID_Unit, ID_Calc and ID_Widget Type.

Each Unit will have one or two (in theory infinite) Calcs, Each Unit/Calc will have have 3 or 4 widgets available to it. About half the time, only 1 widget would be selected,

the rest of the time, ALL of the widgets would be chosen creating 3 to 4 records in my joiner table with redundant Notes, Units and Calcs.

This seems like a normalization violation because its repeating data?

Ignoring that it requires the user to repeatedly choosing the same Unit/Calc to enter all of the widgets.

To improve this I could create something on the form where if the user chooses "ALL" it automatically insersts the 4 records, but that is causing the repeated data.

Or should I make a widget type be "ALL" and then that would just be one record, and I'd have to do something crazy with queries to get it to display properly on a report. Also I try and avoid "dummy" data as much as possible. (Is that a good practice?)

Thanks for reading if you have any advice I'd really appreciate it.

 

[Beetle]

(I'll call it a Joiner table, is there a real name?)

This is typically called a junction table.

Each Unit will have one or two (in theory infinite) Calcs, Each Unit/Calc will have have 3 or 4 widgets available to it. About half the time, only 1 widget would be selected,

the rest of the time, ALL of the widgets would be chosen creating 3 to 4 records in my joiner table with redundant Notes, Units and Calcs.

You need more tables. If a Note can have more than one Unit, then the related data for the Units needs to be in another table, which is a child of the table that stores the relevant data about Notes. Likewise, if a Unit can have more than one Calc, then the Calcs need to be in a table that is the child of the Units table. Ditto for Widgets. These additional "child" tables may indeed need to be junction tables, depending upon the exact nature of the relationships between these entities. I couldn't say for sure without knowing more, but my guess would be that at least one or two of them would be junction tables.