SQL Server many-to-many design recommendation

Question

I have a SQL Server database with two table : Users and Achievements. My users can have multiple achievements so it a many-to-many relation.

At school we learned to create an associative table for that sort of relation. That mean creating a table with a UserID and an AchivementID. But if I have 500 users and 50 achievements that could lead to 25 000 row.

As an alternative, I could add a binary field to my Users table. For example, if that field contained 10010 that would mean that this user unlocked the first and the fourth achievements.

Is their other way ? And which one should I use.

Answer 1

Your alternative way isn't a very good approach at all. Not only is it not queryable (how many people unlocked achievement #10?), but it means nothing. Plus, what are you going to do if you add 5 more achievements? Update all the previous users to add "00000" to the end of their "achievements" column?

There is nothing wrong with the associative table as long as you index it properly. Using that approach the data is infinitly queryable and - perhaps more importantly - makes sense!