First, an example of many-to-many is in the docs. Look for the word "Mapping".

Now about your questions:

1. User has_many user_roles, user_role has a role. You can add more if you want to, but that's enough to ask a user what it's roles are.

2. I don't know what you're talking about, but retrieve_all is a built-in method.

3. You don't retrieve users "together with" roles. You retrieve users, and if you want the roles for a user you ask the user for them.

4. Did you read the article on perl.com?

5. No.

To see the SQL queries, turn on DBI_TRACE (see the DBI docs).