I have added tables to my database by writing SQL commands in a sql folder as specified in the given link. Add a database upgrader / Installer / uninstaller.
What I am exactly looking for is a method to access these tables, do I need to write direct SQL for adding records to the table or I should create corresponding DAO and BAO to access the table.
How to create DAO and BAO for a given table in an extension?
What I did for the sepa extension is a bit of a hack, but worked ok:
write the xml schema files, as you have them on the (development version) core and run xml/phpGencode.php to generate the dao, that you then copy into the extension folders and voila, you got the tables ready to use (for a few more lines, you got the api files, worthwhile IMO)
The process is now detailed in our developer book
What you're looking for is civix generate:entity. It's marked as "EXPERIMENTAL AND INCOMPLETE" on my copy of civix - though my version might be old. Either way, it should point you in the right direction.
UPDATE: While you should still run civix generate:entity, at the time of this writing, it will create a BAO but NOT a DAO file for you. Use Xavier's technique (the accepted answer).
Alternatively, I tend to create the file in sql and then create a DAO in my extension with the few functions that are required. Check https://github.com/CiviCooP/no.maf.oppgavexml/blob/master/CRM/Oppgavexml/DAO/Oppgave.php for an example of a DAO I have copied and pasted into my extension for a table I created in the Upgrade class using
$this->executeSqlFile('sql/create_civicrm_oppgave.sql');
