I am trying to determine the best approach to create a relationship between my notifications table and tables with user information within my current db architecture. The purpose of the notifications table is to allow users to send a notification (to request information) to other users within a specific organization and I'm not sure the ideal relationship and tables that should be involved. Right now my thought is to join on my member table, which is a junction between user and organization and have that member_id as the fk. However, I don't know if this will be beneficial to the information I am really trying to receive with the queries I plan to run. Here are the high level queries I plan on running:
query 1: query all users (with id, first_name, last_name, email) that are within the same organization as the logged in user
query 2: create a record for a notification and pass in the requester user_id (logged in user) and receiver user_id (user within organization being sent a request)
query 3: query current notifications (with first_name, last_name, email)
Here are the current tables in play:
User:
CREATE TABLE `user` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`organization_id` int(11) DEFAULT NULL,
`authentication_token` varchar(255) DEFAULT NULL,
`reset_password_token` varchar(255) DEFAULT NULL,
`reset_password_expires` datetime DEFAULT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `email` (`email`),
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
Organization:
CREATE TABLE `organization` (
`organization_id` int(11) NOT NULL AUTO_INCREMENT,
`organization_name` varchar(255) DEFAULT NULL,
`admin` varchar(255) DEFAULT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
PRIMARY KEY (`organization_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
Member (Junction Table between User and Organization):
CREATE TABLE `member` (
`member_id` int(11) NOT NULL AUTO_INCREMENT,
`member_email` varchar(255) DEFAULT NULL,
`organization_id` int(11) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
`team_id` int(11) DEFAULT NULL,
PRIMARY KEY (`member_id`),
UNIQUE KEY `member_email` (`member_email`),
UNIQUE KEY `member_user_id_organization_id_unique` (`organization_id`,`user_id`),
KEY `user_id` (`user_id`),
CONSTRAINT `member_ibfk_1` FOREIGN KEY (`organization_id`) REFERENCES `organization` (`organization_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `member_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
Notification Table (Currently a One-to-Many on the Member table):
CREATE TABLE `notification` (
`notification_id` int(11) NOT NULL AUTO_INCREMENT,
`requester` int(11) DEFAULT NULL,
`receiver` int(11) DEFAULT NULL,
`message` text,
`status` varchar(255) DEFAULT NULL,
`archived` tinyint(1) DEFAULT NULL,
`member_id` int(11) DEFAULT NULL,
PRIMARY KEY (`notification_id`),
KEY `member_id` (`member_id`),
CONSTRAINT `notification_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `member` (`member_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Best Answer
Query 1:
Query 2:
(I assume you have your id, his id, and other stuff to supply; do not supply
notification_id
, it will be generated.)Query 3 is phrased imprecisely: