MySQL: Structuring Notification Message System So Parts of It Can Be Clicked

Question

For my senior design project, I am working on a project which is an application where an engineer can upload his/her design either 3D, 2D, or 1D and get votes by their customers which design looks good. The issue I am having is structuring my notification to where parts of the notification can be clicked and viewed in detail. If I want to insert a value before, in between, or at the end of the notification message like handles, do I need like a config file with custom messages in the backend and then insert the handles from the frontend? See examples 1 and 2 below.

Example 1 of what the notification should look like:
"Design {design_name} voted by {customer_name} and {50} other customers"

Example 2 of what the notification should look like:
"Your design {design_name} and {10} other designs got votes"

As you can see in the examples above, parts of it can be clicked to see in detail. In example 1, I should be able to click on 50 and view the remaining customers who voted for that particular design. Same concept applies to example 2. Any advice will be greatly greatly appreciated. Thank you.

//Notifications MySQL Table Fields
+----------+----------+----------+----------+-----------+----------+
|   id     | from_id  |   to_id  |  type    | design_id |  viewed  |
+----------+----------+----------+----------+-----------+----------+

//3D Design MySQL Table Fields
+----------+----------+-------------+-------------+------------+
|design_id | user_id  | description | design_name |   image    |
+----------+----------+-------------+-------------+------------+

//2D Design MySQL Table Fields
+----------+----------+-------------+-------------+------------+
|design_id | user_id  | description | design_name |   image    |
+----------+----------+-------------+-------------+------------+

//1D Design MySQL Table Fields
+----------+----------+-------------+-------------+------------+
|design_id | user_id  | description | design_name |   image    |
+----------+----------+-------------+-------------+------------+

user_6495889 · Answer

With the design of the tables as they are, you could get the parameters by the following.

#notification_1
SELECT 
design_name, 
count(*) - 1 as number_of_other_voters, 
voters_table.voters_name
FROM design_table 
LEFT JOIN votes_table ON voted_design_id = design_id 
LEFT JOIN voters_table on votes_table.voter_id = voters_table.id
WHERE design_id = 'x'
GROUP BY design_id

If you wanted to jam everything into a query, then you can do the following (although I highly suggest against it):

#notification_1 
SELECT CONCAT("Design ", design_name, " voted by ", voters_table.voters_name, " and ", count(*) - 1 as number_of_other_voters,  " other customers")
FROM design_table 
LEFT JOIN votes_table ON voted_design_id = design_id 
LEFT JOIN voters_table on votes_table.voter_id = voters_table.id
WHERE design_id = 'x'
GROUP BY design_id

Though this will get you a string, and possibly a meaningful one, this will not achieve the usability goals that you have laid out. If you are using PHP to build a web interface, for example, you would just run the first query and assign the results to a variable, then build your string with the links filled in with some IDs.

foreach($result as $row){
    $notification = "Design " . $row['design_name'] . 
    " voted by " . $row['customer_name'] . 
    " and . " $row['number_of_other_voters'] " . other customers"
}

Any language (JS, PHP, Java, C, etc) will have the ability to load data from a query into a pre-fabricated string with the above method. The second notification would just be a count on the votes table (which you have not shown where the votes are being stored in your example)

SELECT count(*) FROM (SELECT DISTINCT design_id FROM votes_table WHERE user_id = 'x');

That's the best I can do with the tables as they are currently designed (considering you add a user table and a votes table). As pointed out by @Gilbert Le Blank in the comment on the main task, you should only have one table with the ability to mark what the dimension of the design is rather than three design tables unless the data stored in each is fundamentally different.

MySQL: Structuring Notification Message System So Parts of It Can Be Clicked

1 Answers