0

I'm working on a notification system for a website and here's the basic idea; I have a table Notifications where I have columns

notif_msg notif_url receiver_s

where receiver_s will have multiple commas separated values. However, I learned this is not so good idea and was advised to have a different table to store receivers. creating a one to many relationships as specified here. However, I am baffled on few things as I am no guru in MySQL. here's how my create statement looks like following the example from the link above;

create table `notifications` ( `id` int unsigned not null auto_increment,`notif_msg` varchar(500) not null,`notif_url` varchar(100) not null, primary key(`id`));
create table `notifications_target` ( `id` int unsigned not null auto_increment, `notification_id` int unsigned not null, `receiver` varchar(25) not null, index pn_user_index(`notification_id`), foreign key (`notification_id`) references notifications(`id`) on delete cascade,primary key(`id`));

I need to know a few things;

  1. what function does the index serve in there?
  2. from here, how do I insert value into notif_msg, notif_url in notifications table and multiple receivers into receivers column in notification_target referencing a particular row in notifications table?
  3. How do I get values back from notifications table and their corresponding receiver in notification_target table?

here's how I intend to get notifications relating to a specific user and all users at the same time before now;

SELECT notif_msg, notif_url FROM notifications WHERE receiver_s= somename AND WHERE receiver_s=' '

how do I do this now that I have different tables for receivers?

Community
  • 1
  • 1
Victor
  • 7
  • 9

1 Answers1

1

1.what function does the index serve in there?

Read below for more information about indexes in MySQL https://dev.mysql.com/doc/refman/5.7/en/optimization-indexes.html

  1. from here, how do I insert value into notif_msg, notif_url in notifications table and multiple receivers into receivers column in notification_target referencing a particular row in notifications table?

  2. How do I get values back from notifications table and their corresponding receiver in notification_target table?

When u created a one to many mapping, this is the structure you are looking at : One to many

So basically your id is helping you map the rows in another table as notification_id

So to answer your questions :

  1. When you insert rows in second table, make sure their notification_id (which is the foreign key) is same as the id in the main table

  2. When you select rows from second table, add a condition on notification_id if you want to fetch records for a given id, or use join query to fetch results.

For your specific case : refer http://sqlfiddle.com/#!9/47d29/4

Your tables :

create table `notifications` ( `id` int unsigned not null auto_increment,`notif_msg` varchar(500) not null,`notif_url` varchar(100) not null, primary key(`id`));
create table `notifications_target` ( `id` int unsigned not null auto_increment, `notification_id` int unsigned not null, `receiver` varchar(25) not null, index pn_user_index(`notification_id`), foreign key (`notification_id`) references notifications(`id`) on delete cascade,primary key(`id`));

Data :

insert into notifications values (1, 'Hello', '../welcome.html'); 
insert into notifications_target(`notification_id`, `receiver`) values (1, 'Jack'); 
insert into notifications_target(`notification_id`, `receiver`) values (1, 'Marry'); 
insert into notifications_target(`notification_id`, `receiver`) values (1, 'User'); 


insert into notifications values (2, 'Good bye', '../logout.html'); 
insert into notifications_target(`notification_id`, `receiver`) values (2, 'Jack');

Notice that : notification_id in notifications_target table is same as the id in notifications table.

And to fetch records :

Will fetch all the notifications for all the users :

select t1.notif_msg, t1.notif_url, t2.receiver
from notifications t1 inner join notifications_target t2 
on t1.id = t2.notification_id;

Will fetch all the notifications for specific user :

select t1.notif_msg, t1.notif_url, t2.receiver
from notifications t1 inner join notifications_target t2 
on t1.id = t2.notification_id
where t2.receiver = 'Jack';
Himanshu Tyagi
  • 5,201
  • 1
  • 23
  • 43