Someone asked about this on MyResistance and since my reply turned out to be pretty and informative (I guess!) I decided to post it here too. Hopefully you'll find it useful.
Originally Posted by myself
When a PS3 is activated under a given PSN account, it can run the software downloaded (purchased) from the PlayStation store under that PSN account. The activation is automatic the first time you log in with a given PSN account, meaning whatever you download from then on will work fine.
Deactivating a PS3 (or a PSP) basically makes content downloaded under a given PSN account unreadable on that system. You can safely try this yourself. Deactivate your system > try launching any already downloaded game (wont work) > re-activate your system > content will now work fine as before.
This is mostly useful when you want to share stuff you downloaded (purchased) with other people. To do so you have to let them log in as you from their PS3s. So you basically have to give them your log in data (email + password). Once they log in as you, their PS3s are automatically activated (hence, by accessing the "Download List" section on the PS store, they can re-download for free and run on their systems all the stuff you purchased). When a grand total of 5 PS3s are activated under the same PSN account, no more PS3s can be activated, therefore you cannot share your PSN account with more that 4 other people at a time.
If, say, you want to share your PSN account with a new friend but have all those extra 4 activations already in place on other PS3s, you have to de-activate one of these so that your new friend can activate his system under your PSN account.
Also note that deleting a profile via the XMB from a PS3 doesnt automatically deactivate it under the PSN account associated to that profile. It has to be "manually" deactivated from the account management page in order to free one of those 5 activations. So if a friend of yours is using your PSN account and decides to dispose of his PS3 system, tell him to do the deactivation thing (log in as you > Account Management > Activate System > PlayStation 3 (this system) > Deactivate system), otherwise you'll lose one of your possible activations. Simply deleting the profile associated with your PSN account or formatting the HDD is not enough.