2表交互:插入、获取结果、插入 [英] 2-table interaction: insert, get result, insert
问题描述
我需要对我的数据库/网络交互做出根本性的决定，但我缺少找到合适搜索词的知识.
I need to make a fundamental decision of my database/web interaction and I am missing the knowledge to even find proper search terms.
我正在构建一个家庭网站，该网站支持论坛、家谱、带有排名和更多详细信息的 pvp 游戏，所有这些都来自数据模型.目前的技术:Php、MySQL、面向对象的 javascript.
I am building a family website which supports a forum, family tree, pvp games with rankings and more details, all from a datamodel. Technologies right now: Php, MySQL, javascript in object oriented fashion.
在论坛数据模型中，将书面帖子作为新论坛主题(线程)的添加进行处理.
In a forum datamodel, process a written post as addition of a new forum topic (thread).
在我当前的数据模型中，这将暗示并更新两个表:Post 和 Topic.我需要在主题表中插入一行，然后获取新生成的 topicId(sequence)，然后在插入到帖子表中使用它.
In my current datamodel this would imply and update on two tables: Post and Topic. I would need to insert a row in the topic table, then get the newly generated topicId(sequence), and then use that in an insert to the post table.
我觉得这对于需要发生的事情来说太多了，太多的互动.但如果我坚持目前的方法，它将成为一个典型的要求.
I feel this is too much work for what needs to happen, too much interaction. But it will become a typical requirement if I stick with the current approach.
问题:
- 我是在正确的轨道上还是应该
- 重构数据模型或
- 选择另一种数据库交互方式(例如存储过程)
- 我是否面临一个典型的例子，你会在其中使用方法论/框架 xyz.
目前表格具有以下结构(大致基于 erdiagrams 的 这个.com)
Currently tables have following structure (loosely based on this one from erdiagrams.com)
主题:('thread')
TOPIC: ('thread')
id
Forum_ID (FK)
Person_ID (FK)(threadcreator)
IsLocked
IsSticky
Subject
ViewCount
DateCreated
Tc_post_id - trigger to last post_id in this thread
发布
id
topic_id(FK)
person_id(FK)
subject
message
timestamp
replyto
然后我有一个视图，它收集每个主题的最后一个帖子，并通过触发器 Tc_post_id 显示一些相关信息(例如最后一张海报图片).
Then I have a view that collects the last post for each topic and displays some info on that as well (e.g. last poster image) over the trigger Tc_post_id.
推荐答案
广告 1 和 2:您的数据模型很好.在这里使用外键至关重要.您需要注意的另一件事是数据库应确保每个 POST 都有一个 TOPIC 记录.这是通过设置 POST.topic_id NOT NULL 属性来完成的.这是 DB 端的足够安全机制，因为它确保没有 TOPIC 不会留下任何 POST.无论您现在如何处理您的帖子，您都有义务提供一个主题.
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.
广告 3:此处不建议使用带有存储过程的触发器，因为您的 TOPIC 表中有其他数据(IsSticky、IsLocked 等)，您可能希望在创建 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.
广告 4:在业务逻辑方面，您现在可以通过编写自动机制来帮助自己创建 TOPIC 记录，每次在没有指定 topic_id 的情况下创建新的 POST 记录时.我建议为此使用一些 ORM 或利用任何 MVC 框架中可用的数据模型.此类模型的蓝图如下所示:
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;
}
// ...
}
注意:作为一个好的 MVC 实践，这些模型应该是直接对表行进行操作的唯一地方.否则，您最终会遇到 SQL 错误(但数据模型将保持一致，因此您不必担心会出现问题).
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).
最后在 controller 层利用您的模型:
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
}
}
}
这篇关于2表交互:插入、获取结果、插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
