This is a specific question to DotNetOpenAuth. I opened up the .NET template, and found that there are 2 tables "Nonce" and "OpenIDAssociation" and there are 2 SPs that do the cleanup upon on expiration date.
What exactly do these 2 tables do? Why do we need to keep track of the association?
Nonces provides replay protection and OpenID associations allow for faster logins and (in some cases) enhanced security. While nonces and associations can be stored in memory without a database table behind it, a database is more secure because nonces won't be lost from memory when a web app restart causes memory to be cleared. Also, in web farms a database is the primary way to share state between web servers in the farm, so these tables facilitate that.