title: Bulletin board - Database optimisation
body: <p>
  This question is a follow on from this 
  <a href="https://stackoverflow.com/questions/4288359/mysql-nested-selec">Question</a>
  </p>
  
  <h2>The project and problem</h2>
  
  <p>The project I am currently working on is a bulletin board for a large non-profit organisation. The bulletin board will be used to allow inter-office communication within the organisation.</p>
  
  <p><p>I am building the application and have been having trouble extracting the results that I need from my database because I don't think it is properly normalized and because of limitations in my knowledge of relational database theory and mysql. </p><p>I would appreciate input into the design of the board in general and in particular, ways that the database structure can be improved to facilitate efficient queries and help me develop this application and future application faster</p></p>
  
  <h2>Business Logic</h2>
  
  <p>The bulletin board will be used in the following way</p>
  
  <ol>
  
  
  <li><b> Posting bulletins and responses to bulletins</b>
    <ol>
      <li>Employees or 'users' in offices around the country will be able to post messages to the bulletin board.Bulletins must be posted to a location and categorised- i'll call these "bulletins".</li>
      <li>Users will be able to post any number of replies to any one bulletin and users will be able to reply to their own bulletin - i'll call these 'replies'.</li>
    </ol>
  </li>
  
  <li><b>Rating bulletins and replies</b>
  
      <ol><li>Users will be able to either 'like' or 'dislike' a bulletin or a reply and the total number of likes or dislikes will be shown for each bulletin or reply. </li>
    </ol> 
  </li>
  
  <li><b>Viewing the bulletin board and responses</b>
    <ol>
      <li>Bulletins can be displayed chronologically.</li>
      <li> Users can sort bulletins chronologically or chronologically by the latest reply to that bulletin(let me know if you need more explanation)</li>
      <li>When a particular bulletin is selected, replies to that bulletin will be displayed chronologically</li>
    </ol>
  </ol>
  
  <pre><code>-- phpMyAdmin SQL Dump
  -- version 3.2.4
  -- http://www.phpmyadmin.net
  --
  -- Host: localhost
  -- Generation Time: Jan 16, 2011 at 06:44 PM
  -- Server version: 5.1.41
  -- PHP Version: 5.3.1
  
  SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
  
  --
  -- Database: `bulletinboard`
  --
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `bbs`
  --
  
  CREATE TABLE IF NOT EXISTS `bbs` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `bb_locations_id` int(11) NOT NULL,
    `bb_categories_id` int(11) NOT NULL,
    `users_id` int(11) NOT NULL,
    `title` varchar(255) NOT NULL,
    `content` text NOT NULL,
    `created_date` int(11) NOT NULL,
    `rank` int(11) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=87 ;
  
  --
  -- Dumping data for table `bbs`
  --
  
  INSERT INTO `bbs` (`id`, `bb_locations_id`, `bb_categories_id`, `users_id`, `title`, `content`, `created_date`, `rank`) VALUES
  (83, 8, 28, 44, 'sdaf', 'asdfasdf', 1292712797, 0),
  (84, 8, 28, 44, 'asdf', 'asdfasd', 1292875089, 0),
  (86, 8, 28, 44, 'Robert is leaving', 'Robert is leaving and going back to the states ', 1294344916, 0);
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `bb_categories`
  --
  
  CREATE TABLE IF NOT EXISTS `bb_categories` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(255) NOT NULL,
    `description` varchar(255) NOT NULL,
    `list_order` varchar(255) NOT NULL,
    `admin` int(11) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=30 ;
  
  --
  -- Dumping data for table `bb_categories`
  --
  
  INSERT INTO `bb_categories` (`id`, `title`, `description`, `list_order`, `admin`) VALUES
  (28, 'Travel', 'Rideshares, proposed trips etc', '1', 1);
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `bb_locations`
  --
  
  CREATE TABLE IF NOT EXISTS `bb_locations` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(255) NOT NULL,
    `description` varchar(255) NOT NULL,
    `address` varchar(255) NOT NULL,
    `post_code` int(11) NOT NULL,
    `list_order` int(11) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;
  
  --
  -- Dumping data for table `bb_locations`
  --
  
  INSERT INTO `bb_locations` (`id`, `title`, `description`, `address`, `post_code`, `list_order`) VALUES
  (8, 'Washington DC', 'asdkf', 'dsf', 0, 1);
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `bb_ratings`
  --
  
  CREATE TABLE IF NOT EXISTS `bb_ratings` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `bbs_id` int(11) NOT NULL,
    `users_id` int(11) NOT NULL,
    `like_id` int(2) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=68 ;
  
  --
  -- Dumping data for table `bb_ratings`
  --
  
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `bb_replies`
  --
  
  CREATE TABLE IF NOT EXISTS `bb_replies` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `users_id` int(11) NOT NULL,
    `bbs_id` int(11) NOT NULL,
    `content` text NOT NULL,
    `created_date` int(11) NOT NULL,
    `rank` int(11) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=158 ;
  
  --
  -- Dumping data for table `bb_replies`
  --
  
  INSERT INTO `bb_replies` (`id`, `users_id`, `bbs_id`, `content`, `created_date`, `rank`) VALUES
  (156, 44, 86, 'good ridance i say\r\n', 1294788444, 0),
  (157, 44, 86, 'And stay away\r\n', 1294892751, 0);
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `bb_reply_ratings`
  --
  
  CREATE TABLE IF NOT EXISTS `bb_reply_ratings` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `bb_replies_id` int(11) NOT NULL,
    `users_id` int(11) NOT NULL,
    `like_id` tinyint(11) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=115 ;
  
  --
  -- Dumping data for table `bb_reply_ratings`
  --
  
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `bb_sort_bys`
  --
  
  CREATE TABLE IF NOT EXISTS `bb_sort_bys` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(20) NOT NULL,
    `description` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
  
  --
  -- Dumping data for table `bb_sort_bys`
  --
  
  INSERT INTO `bb_sort_bys` (`id`, `title`, `description`) VALUES
  (1, 'Newest', 'Posts are sorted by their creation date'),
  (2, 'Popular', 'Posts are sorted by the date of their lates reply, or by post date if they have now replies');
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `users`
  --
  
  CREATE TABLE IF NOT EXISTS `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_name` varchar(10) NOT NULL,
    `first_name` varchar(100) NOT NULL,
    `last_name` varchar(100) NOT NULL,
    `permission` int(1) NOT NULL,
    `bb_sort_bys_id` varchar(10) NOT NULL,
    `bb_locations_csv` varchar(255) NOT NULL,
    `defaultLocation` int(11) NOT NULL,
    `bb_categories_csv` varchar(255) NOT NULL,
    `total_bulletins` int(5) NOT NULL,
    `bulletins_per_page` int(5) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=45 ;
  </code></pre>