Performance gain or less using an association table even when there is just a one-to-many relationship

Answer 1

Selecting data and searching will mean more joins : you'll have to work on 3 tables instead of 2.

Inserting data will mean more insert queries : you'll have to insert to 3 tables instead of 2.

So I'm guessing this could mean a bit more work -- which, in turns, might hurt performances a bit.

Answer 2

Because this is one-to-many I'd personally not use an association table, it's totally unnecessary.

The performance hit from this decision won't be too great. But think about the context of your data too, don't just do it because some program tells you - understand your data.

Answer 3

"The fastest way to do anything is not to do it at all." -- Cary Millsap, Optimizing Oracle Performance.

"A designer knows he has achieved true elegance not when there is nothing left to add, but when there is nothing left to take away." -- Antoine de Saint-Exupéry

The simpler implementation has two tables and three indexes, two unique. The more complicated implementation has three tables and five indexes, four unique. The index on asso_countries_players.player_name (which should be a surrogate ID -- what happens if a player's name changes, like if they get married or legally change it, as Chad Ochocinco (nee Johnson) did?) must also be unique to enforce the 0..1 nature of the relationship between players and countries.

If the associative entity isn't required by the data model, then eliminate it. It's generally pretty trivial to transform a 0..1 relationship or 1..n relationship to an n..n relationship:

1. Add associative entity (and I'd question the need for a surrogate key there unless the relationship itself had attributes, like a start or end date)
2. Populate associative entity with existing data
3. Reimplement the foreign key constraints
4. Remove superseded foreign key column in child table.