If you write the uninstall hook to drop tables it becomes and option for the user to select. They are presented with an "uninstall" tab on the modules page.  After disabling a module the user can click this tab to confirm dropping databases.  In short Drupal's default functionality is extremely similar to what you describe.
<br><br><br><div><span class="gmail_quote">
On 7/29/07, <b class="gmail_sendername">Vasileios Lourdas</b> &lt;<a href="mailto:lourdas_v@yahoo.gr" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">lourdas_v@yahoo.gr</a>&gt; wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">

Hello,<br><br>I&#39;ve been developing a module for a Drupal 5.2 site of mine. The module<br>creates two additional database tables, during the installation procedure.<br><br>My question is: In case I want to disable the module and then uninstall it, I
<br>would like to ask the user if he would like the tables dropped or not. Can<br>something like this be done in the uninstall() hook? What I would like is to<br>prompt the user with an option &quot;Do you want the tables dropped?&quot; and
<br>according to his input, the corresponding action would be performed.<br><br>Regards,<br>--<br># Vasileios Lourdas,<br># Informatics Engineer, Thessaloniki (Greece)<br># <a href="http://www.lourdas.name" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
http://www.lourdas.name
</a><br>--<br>[ Drupal support list | <a href="http://lists.drupal.org/" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">http://lists.drupal.org/</a> ]<br></blockquote></div><br><br clear="all"><br>
-- <br><br>Kevin Bridges<br><a href="http://www.kevinbridges.org/" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">http://www.kevinbridges.org/
</a>