Understanding database structure for auto-migration
You can skip migrating the following tables:


accesslog
cache_*
sessions
watchdog


All the above contains material that you generally don't want to migate across sites.

The contents of the tables for built-in search will be regeneated by cron if you don't migrate them, but it may take up a lot of resouces if you've a lot content. But if this is not a concern, you can also skip migrating:


search_*


As for table names, Drupal relies heavily on namespace dicipline to avoid collisions.  So a module named foobar should only create tables with a name that starts with foobar_ (or just foobar if it only creates a single table).  In other words, you can tell what tables in the database that "belongs" to what module by their name.  This goes for both core modules and for regular contributed modules.  As for custom modules (i.e. modules that is managed outside the Drupal.org framework, you may find exceptions to this naming convention - but only if they're written by bozos :-).

If you look at your database, you may discover a lot of tables with names starting with field_.  These are of course from the (core) Field module, but I mention these specifically because these are the tables used for custom content types that are created using "Fields in core" (a key Drupal feature).

As for interdependencies between tables, looking at the fields in the tables that belong to the same module is usually enough.  Getting familiar with the Schema API is also helpful.  But to be honest: Sometimes you need to sit down and read source code.

As for creating and dropping tables, well behaved modules create datebase tables using hook_schema (found inside the module's .install-file) upon installation, and automatically drop the same tables upon un-installation.

Note that just pushing a module with git doesn't install the module.  It just makes it available for installation.  To install it, you either do it manually (using the administrator's GUI), or script it using Drush.

It is also useful to know that there are clear rules in the Drupal community of practice that a module should only write to its own tables.  If a client module want to write to another module's table, it must do so through the module's API, not by interacting with the database.  Module interdependency is tracked in the module's .info-file.  This means, for instance, that you'll not be able to disable a service module a client module relies on (provided their interdependency has been correctly declared).