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.
