0

I'm trying to create a very simple messaging system using PHP and MySQL but I'm having some issues to decide how to structure things here.

A quick list of what I need to achieve.

  • The system needs to allow both registered users and guests send messages.
  • The messages will be received by the customer service department (this means that customers can't send messages to other customers).
  • The messages can come from 1) a contact form available in the Contact page of the website where are asked fist name, last name, email, mobile phone, subject and message; and 2) emails sent by users (then the system will receive the emails via webhook and store them properly).
  • The customer service department needs to able to reply to any message.
  • The customer service department can contact any user (registered or not) at any time. This means that they can reply to messages but also can "start" the conversation (example: A message asking to confirm/update details).

I started the design but I'm completely stuck at the moment. Everything is fine when there are only registered users and customer service members: I just need to relate each message to a user_id as sender or receiver. But, when we add guest users, the user_id is useless, because they are not registered (so no user id). This means I will need to store all first name, last name, email and mobile phone for them (creating redundant data in the case of registered users, because I already have that data for them)

The tables will contain a column created_at.

enter image description here

My problem is: guest users won't have a record in the users table, so I cannot just have a user_id field on the messages tables. Also, I need to tell who is sending the message (because our customer service department can send messages to registered/guest users).

Honestly I cannot find a way to achieve this without having columns that will be equal to null in most of the cases.

Any help on this will be appreciated.

Thanks in advance

TJ is too short
  • 827
  • 3
  • 15
  • 35

2 Answers2

0

You will need to have a record added for the guest, as a user account in the database. Set default values, and log his IP address so should he decide to sign up, you will update the guest record with the new user account.

Add an expiry and a cron job to delete any guest accounts which are inactive for x days.

Chris Bartolo
  • 41
  • 5
  • Thanks for the suggestion but I would really like to avoid that because if I create records for guest users, I won't be able to delete them (because if I delete them, what happens with the messages related to them?) – TJ is too short Jul 03 '17 at 13:55
  • It depends on how you set the foreign key relation. – Chris Bartolo Jul 03 '17 at 15:25
0

You have encountered an example where the relational model doesn't neatly fit. It's a known issue, with several Stack Overflow posts.

The good news is - there's no "clean" solution in database design. There are a few common models, but they all have varying degrees of unpleasantness.

The bad news - you'll have to make the trade-offs, and live with the consequences.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52