1

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;
  • Typo: Where is annotation_request_id defined? Fix that and check for other inconsistencies. – Rick James Nov 5 '16 at 19:40
  • @RickJames I apologize for the mix up and thank you for pointing that out. I updated the code to reflect the current state. – cphill Nov 6 '16 at 15:45
1

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;
  • So based on your queries it appears you think I should join on the user table rather than the member table. Is there a reason for this join over a junction table join? Should I ever join on a junction? – cphill Nov 6 '16 at 15:39
  • Redundant information is a no-no in database design. You have a 1:many relationship in user for organization_id:user_id, then you have a many:many relationship in member. Which is it? Then either remove organization_id column or remove member table. – Rick James Nov 6 '16 at 16:07
  • Great point. If I were to remove the 1:many and keep the member table, would you still think it was a good idea to join the notifications table on the member table or should this join happen elsewhere? – cphill Nov 6 '16 at 19:16
  • @cphill - Can you sketch out the table definitions and the queries? (And in doing so, you may answer your own question.) – Rick James Nov 12 '16 at 1:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.