MySQL Table Schema Design – Relationship with Junction or User Table

database-designMySQLschema

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:

SELECT them.user_id, them.first_name, them.last_name, them.email
    FROM user AS me
    JOIN user AS them  ON me.organization_id = them.organization_id
    WHERE me.user_id = ?

Query 2:

INSERT INTO notification (requester, receiver, ...)
    VALUES
    (?, ?, ...)

(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:

SELECT first_name, last_name, email,
       timestamp,   -- Don't you need a date&time in the table?
       ...   -- don't you want more fields?
    FROM notifications
    WHERE email = ?  -- Leave out if you want all users; change to search by name
    ORDER BY `timestamp` DESC  -- I assume by 'current' you mean 'recent'?
    LIMIT 10;