stackoverflow如何找到用户给他们的通知？

本文介绍了stackoverflow如何找到用户给他们的通知？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在创建一个像SO这样的网站。现在我想知道，当我在杰克的回答/问题下写下评论时，会发生什么？我们向杰克发送通知，对吧？那么如何找到杰克？

换句话说，我应该将author-user-id存储在Votes / Comments表中吗？这是我目前的投票表结构：

  //投票
 + ---- + ---- ----- + ------------ + --------- + ------- + ------------ + 
 | id | post_id | table_code | user_id |值|时间戳| 
 + ---- + --------- + ------------ + --------- + ------- + ------------ + 
 // ^这个列存储发送投票的用户ID 
 // ^因为有多个帖子表（专注于编辑）

现在我想发送一个关于后所有者的通知。但我不知道怎么找到他？我应该在名为所有者的投票表上添加一个新列，并存储author-id？

编辑：我必须提到我有四个帖子表（我知道这个结构是疯狂的，但实际上，这些的结构表格的结构是非常不同的，我不能仅仅创建一个表）。这样的东西：

  // Posts1（table_code：1）
 + ---- + ---- --- + ----------- + 
 | id |标题|内容| 
 + ---- + ------- + ----------- + 
 
 // Posts2（table_code：2）
 + ---- + ------- + ----------- + ----------- + 
 | id |标题|内容| author_id | 
 + ---- + ------- + ----------- + ----------- + 
 
 //帖子3（table_code：3）
 + ---- + ------- + ----------- + ----------- + 
 | id |标题|内容| author_id | 
 + ---- + ------- + ----------- + ----------- + 
 
 // Posts4（table_code：4）
 + ---- + ------- + ----------- + 
 | id |标题|内容| 
 + ---- + ------- + ----------- +

但是，只是其中一些 Post 表有 author_id 列（因为我有两个不是由用户制作的帖子表。所以，如你所见，我不能在这些帖子表中创建一个外键。

我需要什么：我想要一个在的插入后触发投票表发送通知给 author 如果有一个 author_id 列。（或者如果有 author_id ），则返回 author_id 的查询。或者无论如何，我的问题都是一个很好的解决方案。

解决方案

Votes.post_id 应该是 Posts 表中的外键。从那里你可以得到 Posts.author_id ，并将通知发送给该用户。

随着您的多个 posts＃表，你不能使用真正的外键。但是，您可以根据 table_code 值编写一个 UNION 查询，并将其与适当的表连接。

  SELECT p.author_id 
 FROM Votes AS v 
 JOIN Posts2 AS p ON p.id = v.post_id 
 WHERE v.table_code = 2 
 UNION 
 SELECT p.author_id 
 FROM Votes AS v 
 JOIN Posts3 AS p ON p.id = v.post_id 
 WHERE v .table_code = 3

尽量避免存储可以通过按照外键获取的数据，以便信息只存储在一个地方。如果由于过度加入而遇到性能问题，您可能需要违反这种规范化原则，但只能作为最后的手段。

I'm creating a website like SO. Now I want to know, when I write a comment under Jack's answer/question, what happens? SO sends a notification to Jack, right? So how SO finds Jack?

In other word, should I store author-user-id in the Votes/Comments tables? Here is my current Votes-table structure:

// Votes
+----+---------+------------+---------+-------+------------+
| id | post_id | table_code | user_id | value | timestamp  |
+----+---------+------------+---------+-------+------------+
//                               ^ this column stores the user-id who has sent vote
//                   ^ because there is multiple Posts table (focus on the Edit)

Now I want to send a notification for post-owner. But I don't know how can I find him? Should I add a new column on Votes table named owner and store the author-id ?

Edit: I have to mention that I have four Posts tables (I know this structure is crazy, but in reality the structure of those Posts tables are really different and I can't to create just one table instead). Something like this:

// Posts1 (table_code: 1)
+----+-------+-----------+
| id | title |  content  |
+----+-------+-----------+

// Posts2 (table_code: 2)
+----+-------+-----------+-----------+
| id | title |  content  | author_id |
+----+-------+-----------+-----------+

// Posts3 (table_code: 3)
+----+-------+-----------+-----------+
| id | title |  content  | author_id |
+----+-------+-----------+-----------+

// Posts4 (table_code: 4)
+----+-------+-----------+
| id | title |  content  |
+----+-------+-----------+

But the way, Just some of those Post tables have author_id column (Because I have two Posts tables which are not made by the users). So, as you see, I can't create a foreign key on those Posts tables.

What I need: I want a TRIGGER AFTER INSERT on Votes table which send a notification to the author if there is a author_id column. (or a query which returns author_id if there is a author_id). Or anyway a good solution for my problem ...

解决方案

Votes.post_id should be a foreign key into the Posts table. From there you can get Posts.author_id, and send the notification to that user.

With your multiple Posts# tables, you can't use a real foreign key. But you can write a UNION query that joins with the appropriate table depending on the table_code value.

SELECT p.author_id
FROM Votes AS v
JOIN Posts2 AS p ON p.id = v.post_id
WHERE v.table_code = 2
UNION
SELECT p.author_id
FROM Votes AS v
JOIN Posts3 AS p ON p.id = v.post_id
WHERE v.table_code = 3

Try to avoid storing data that you can get by following foreign keys, so that the information is only stored one place. If you run into performance problems because of excessive joining, you may need to violate this normalization principle, but only as a last resort.

这篇关于stackoverflow如何找到用户给他们的通知？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！