How does stackoverflow find users to give them their notifications?
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 ...
Answer
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.