Any special reason why you need a custom lookup field on Account? That's what AccountContactRole object is for (as you mentioned in the question).
It's a junction object though (has 2 masters) which means you can't make a lookup to it (similar to OpportunityContactRoles, OpportunityLineItems etc).
If you need it really as lookup (to use in some formulas I guess?) consider filling it in with a trigger or something (based on Contact Roles), not letting users pick it. Filtered lookups are bit annoying for users as they'd need to know what to type in the search or view the Contact before using the lookup (so it appears on their "Recently viewed" in the lookup window).
Here's the out of the box functionality (sample data found in any Developer Edition):
Not all Contacts need to have a Role. Contact can have more than 1 Role. Only one can have "Primary" ticked (if you set it, SF will automatically remove it for you from all others, even if you'll try to cheat with a mass update from Apex or Data Loader for example).
Contacts that aren't related to this company can be selected too (imagine a company that has HQ office, a branch office and common accounting department - you could use person from HQ Accounting as a Role in the child company).
You can modify the Roles picklist in the setup. You can't add fields to the object or make lookups to it. As far as I know there's no "magic" happening when you make an Opportunity (if you'd want to automatically copy this data to Opportunity Contact Roles you'd need a trigger. But since each Opp should be unique and could have different key stakeholders maybe it's good).
I think that's pretty much it, check help for more? Not sure what more "out of the box" you'd want that would be generic enough to work OK for all businesses...
Last but not least - Entity Relationship Diagram :)