How to add a node field to a database table in a custom module
Most of the time you will probably want to use the field API (CCK module in D6) rather than adding custom tables or modifying node by yourself. 

Adding custom tables was a very D5 way of doing thing. 

If you want to do this you will need to define your tables using hook_schema() and then in your mymodule_install() hook you can call drupal_install_schema() this should create a new table which represents your module. 

It is worth mentioning that your schema should always represent your current schema. If you add a column you should alter the schema so that someone who installs your module will get the correct schema, but also write an update function to alter the table so that existing users get the correct new schema. 

It is possible to alter other modules' tables, but I think that should be covered in another question.