Should I redesign my tables or can I make this work?

Question

Right now I'm working on expanding my website to new functionality. I want to enable notifications from different sources. Similar to groups and people on facebook. Here is my table layout right now.

course_updates
id | CRN (id of course) | update_id
------------------------------------
courses
id | course_name | course_subject | course_number
-------------------------------------------------
users
id | name | facebook_name
---------------------------------------------------

user_updates
id | user_id | update_id
------------------------

updates
id | timestamp | updateObj
---------------------------

What I would like to be able to do is take course_update and user_updates in one query and join them with updates along with the correct information for the tables. So for course_updates i would want course_name, course_subject, etc. and for user_updates i would want the username and facebook name. This honestly probably belongs in two separate queries, but I would like to arrange everything by the timestamp of the updates table, and I feel like sorting everything in php would be inefficient. What is the best way to do this? I would need a way to distinguish between notification types if i were to use something like a union because user_updates and course_updates can store a reference to the same column in updates. Any ideas?

Answer 1

If you are going to merge the two table you need to keep in mind 2 things:

1. Number of columns should ideally be the same
2. There should be a way to distinguish the source of the data.

Here is one way you could do this:

SELECT * FROM
   (SELECT courses.course_name as name, courses.course_subject as details,
           updates.updateObj as updateObj, updates.timestamp as timestamp,
           "course" as type
    FROM courses, updates, course_updates
    WHERE courses.id = course_updates.course_id
    AND course_updates.udpate_id = updates.id)
   UNION ALL
    SELECT users.name as name,users.facebook_name as details,
           updates.updateObj as updateObj,updates.timestamp as timestamp,
           "user" as type
    FROM users ,updates, user_updates
    WHERE users.id = user_updates.user_id
    AND  user_updates.update_id = updates.id) as out_table
ORDER BY out_table.timestamp DESC

The type will let you distinguish between user and course updates and could be used by your front end to differently colour the rows. The course_id does not appear in this but you can add it, just keep in mind that you will have to add some dummy text to the user select statement to ensure both queries return the same number of rows. Note that in case there is an update referring to both user and course, it will appear twice.

You could also order by type to differentiate user and course data.

Answer 2

You might not need updates table at all. You can include timestamp columns to course_updates and user_updates tables

CREATE TABLE course_updates
(
  `id` int, 
  `CRN` int, 
  `timestamp` datetime -- or timestamp type
);
CREATE TABLE user_updates
(
  `id` int, 
  `user_id` int, 
  `timestamp` datetime -- or timestamp type
);

Now to get an ordered and column-wise unified resultset of all updates you might find it convenient to pack update details for each update type in a delimited string (using CONCAT_WS()) in one column (let's call it details), inject a column to distinguish an update type (lets call it obj_type) and use UNION ALL

SELECT 'C' obj_type, u.id, u.timestamp, 
        CONCAT_WS('|', 
                  c.id, 
                  c.course_name, 
                  c.course_subject, 
                  c.course_number) details
  FROM course_updates u JOIN courses c
    ON u.CRN = c.id
 UNION ALL
SELECT 'U' obj_type, u.id, u.timestamp, 
       CONCAT_WS('|', 
                 s.id, 
                 s.name, 
                 s.facebook_name) details
  FROM user_updates u JOIN users s
    ON u.user_id = u.id
 ORDER BY timestamp DESC

Sample output:

| OBJ_TYPE | ID |                   TIMESTAMP |                 DETAILS |
-------------------------------------------------------------------------
|        C |  3 | July, 30 2013 22:00:00+0000 | 3|Course3|Subject3|1414 |
|        U |  2 | July, 11 2013 14:00:00+0000 |        1|Name1|FB Name1 |
|        U |  2 | July, 11 2013 14:00:00+0000 |        3|Name3|FB Name3 |
...

Here is SQLFiddle demo

You can then easily explode details values while you iterate over the resultset in php.

Answer 3

I don't think you should mix both of those concepts (user and course) together in a query. They have different number of columns and relate to different concepts.

I think you really should use two queries. One for users and one for courses.

SELECT courses.course_name, courses.course_subject, courses.course_number,   
       updates.updateObj,updates.timestamp
FROM courses, updates, course_updates
WHERE courses.id = course_updates.course_id
    AND course_updates.udpate_id = updates.id
ORDER BY updates.timestamp;

SELECT users.name,users.facebook_name,updates.updateObj,updates.timestamp
FROM users ,updates, user_updates
WHERE users.id = user_updates.user_id
    AND  user_updates.update_id = updates.id
ORDER BY updates.timestamp;