Ad 1 and 2: Your data model is fine. Using foreign keys is crucial here. One more thing that you need to take care of is that the database should ensure there is a TOPIC record for each POST. This is done by setting POST.topic_id NOT NULL attribute. This is sufficient safety mechanism on the DB side, as it ensures that no POST will be left without TOPIC. No matter what you do now with your POST you are obligated to provide a TOPIC.
Ad 3: A trigger with stored procedure is not recommended here as you have additional data in your TOPIC table (IsSticky, IsLocked, etc), which you might want to provide upon TOPIC record creation. Also, if such a trigger would be applicable, the database design would be a subject to denormalization.
Ad 4: On the business logic side you can now aid yourself by writing a automated mechanism to create the TOPIC record every time a new POST record is created without specified topic_id. I recommend using some ORM for this or take advantage of the data models available in any MVC framework. The blueprint for such models would look like this:
abstract class AModel // this class should be provided by ORM or framework
{
/**
* @var PDO
*/
protected $_db_driver;
public function getLastInsertId()
{
$stmt = $this->_db_driver->prepare('SELECT LAST_INSERT_ID() AS id');
$stmt->execute();
return $stmt->fetch(PDO::FETCH_OBJ)->id;
}
public abstract function getFieldList();
}
class ForumTopicModel extends AModel
{
public function insert(array $data)
{
$sql = 'INSERT INTO topic VALUES (:id, :forum_id, :person_id, :is_locked, ...)';
$stmt = $this->_db_driver->prepare($sql);
return $stmt->execute($data);
}
public function getFieldList()
{
return array('id', 'forum_id', 'person_id', 'is_locked', /*...*/);
}
// ...
}
class ForumPostModel extends AModel
{
public function insert(array $data)
{
$sql = 'INSERT INTO post VALUES (:id, :topic_id, :person_id, :subject, ...)';
$stmt = $this->_db_driver->prepare($sql);
return $stmt->execute($data);
}
public function getFieldList()
{
return array('id', 'topic_id', 'person_id', 'subject', /*...*/);
}
public function insertInitialTopicPost(array $form_data)
{
$this->_db_driver->beginTransaction();
$result = true;
if ( empty($form_data['topic_id']) ) {
// no topic_id provided, so create new one:
$topic = new ForumTopicModel();
$topic_data = array_intersect_key(
$form_data, array_flip($topic->getFieldList())
);
$result = $topic->insert($topic_data);
$form_data['topic_id'] = $topic->getLastInsertId();
}
if ( $result ) {
$forum_post_data = array_intersect_key(
$form_data, array_flip($this->getFieldList())
);
$result = $this->insert($forum_post_data);
}
if ( $result ) {
$this->_db_driver->commit();
}
else {
$this->_db_driver->rollBack();
}
return $result;
}
// ...
}
Note: as a good MVC practice those models should be the only place to directly operate on the table rows. Otherwise you'll end up getting SQL errors (but the data model will remain coherent, so you don't have to worry that something will break).
Finally take advantage of your models in the controller layer:
class ForumPostController extends AController
{
public function createInitialTopicPostAction()
{
$form_data = $this->getRequest()->getPost(); /* wrapper for getting
the $_POST array */
// (...) validate and filter $form_data here
$forumPost = new ForumPostModel();
$result = $forumPost->insertInitialTopicPost($form_data);
if ( $result ) {
// display success message
}
else {
// display failure message
}
}
}
`s. – Charles Dec 16 '12 at 21:38