The following snippet shows, how to create database tables in a setup script. It uses no direct queries against the database but the an object oriented way Magento does such things. (This snipped assumes  that your resource setup model is derived from Mage_Core_Model_Resource_Setup)

<?php
/**
 *
 * Magento
 *
 * NOTICE OF LICENSE
 *
 * This source file is subject to the Open Software License (OSL 3.0)
 * It is  available through the world-wide-web at this URL:
 * http://opensource.org/licenses/osl-3.0.php
 *
 *
 * @category   AskSheldon
 * @package    AskSheldon_FeaturedProducts
 * @copyright  Copyright (c) 2016 Marcel Lange (https://www.ask-sheldon.com)
 * @license    http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
 * @author     Marcel Lange <info@ask-sheldon.com>
 *
 * Date:       10.05.16
 * Time:       14:53
 *
 * Creates table structure for category -> featured product relation
 *
 * Mage_Core_Model_Resource_Setup
 *
 *  *
 * Created by IntelliJ IDEA.
 *
 */
/* @var $installer Mage_Core_Model_Resource_Setup */
$installer = $this;

$installer->startSetup();

/**
 * Create table 'asksheldon_featuredproducts/category_featuredproducts'
 */
$table = $installer->getConnection()
    ->newTable($installer->getTable('asksheldon_featuredproducts/category_product')) # returns instance of Varien_Db_Ddl_Table
    ->addColumn('category_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'unsigned' => true,
        'nullable' => false,
        'primary' => true,
        'default' => '0',
    ), 'Category ID')
    ->addColumn('product_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'unsigned' => true,
        'nullable' => false,
        'primary' => true,
        'default' => '0',
    ), 'Product ID')
    ->addColumn('position', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'nullable' => false,
        'default' => '0',
    ), 'Position')
    ->addIndex(
        $installer->getIdxName(
            'asksheldon_featuredproducts/category_product',
            array('product_id')
        ),
        array('product_id'))
    ->addForeignKey(
        $installer->getFkName(
            'asksheldon_featuredproducts/category_product',
            'category_id', 'catalog/category',
            'entity_id'
        ),
        'category_id',
        $installer->getTable('catalog/category'),
        'entity_id',
        Varien_Db_Ddl_Table::ACTION_CASCADE,
        Varien_Db_Ddl_Table::ACTION_CASCADE
    )
    ->addForeignKey(
        $installer->getFkName(
            'asksheldon_featuredproducts/category_product',
            'product_id',
            'catalog/product',
            'entity_id'
        ),
        'product_id',
        $installer->getTable('catalog/product'),
        'entity_id',
        Varien_Db_Ddl_Table::ACTION_CASCADE,
        Varien_Db_Ddl_Table::ACTION_CASCADE
    )
    ->setComment('AskSheldon Features Product To Category Linkage Table');
$installer->getConnection()->createTable($table);

$installer->endSetup();

As you can see, it is also possible to add indexes and foreignkeys by using a Varien_Db_Ddl_Table instance.

Here are the database related parts of the config.xml:

<?xml version="1.0"?>
<config>

    <!-- ... -->
    
    <global>
        <models>
            <asksheldon_featuredproducts>
                <class>AskSheldon_FeaturedProducts_Model</class>
                <resourceModel>asksheldon_featuredproducts_resource</resourceModel>
            </asksheldon_featuredproducts>
            <asksheldon_featuredproducts_resource>
                <class>AskSheldon_FeaturedProducts_Model_Resource</class>
                <entities>
                    <category_product>
                        <table>asksheldon_featuredproducts_category_product</table>
                    </category_product>
                </entities>
            </asksheldon_featuredproducts_resource>
        </models>
        <resources>
            <asksheldon_featuredproducts_setup>
                <setup>
                    <module>AskSheldon_FeaturedProducts</module>
                </setup>
            </asksheldon_featuredproducts_setup>
        </resources>
    </global>

    <!-- ... -->

</config>