Many-to-Many Grouped into one larger table?

[vapid2323]

I wanted to see if there is any issue with combining a bunch of smaller many to many fields into a larger table.

For instance I have my Staff Table that is used quite a bit in my forms via a Multivalve field (I know never use them) and I am now changing over to a traditional many to many. What stopping me from just putting all that into one large junction table?

 

[The_Doc_Man]

You want to be able to create a valid relationship in order to use a many-to-many junction. The junction's FK fields have to point to PK fields in the one-side tables being joined this way. However, having that relationship is going to screw around with your ability to request relational integrity to be enforced. That is because if you merged two such tables, there would a question as to whether an arbitrary many/many record corresponded to tables A+B or tables C+D. That ambiguity would make your tables not updateable and badly confusing.

The FK fields would also violate normalization since the value of the dependent field would not only depend on the value of the independent table, but also would depend on which of multiple tables was actually the origin of the data. That dependence on a non-key factor violates normalization rules.

 

[vapid2323]

Thank you for that feedback! Sounds like I will go with what works and stop trying to create shortcuts