1

I have a table that I will be using to save "template" messages for my users. The schema is 

CREATE TABLE listemailtemplates (
    id int primary key auto_increment,
    userId int not null,
    message text,
    date datetime default current_timestamp
);

The gist of it is, when a user sends out an email to a customer, their message is saved as a template, so the next time they email a customer, they can select it from a drop down.

Right now the functionality required is simple - its not based on how many times a template is used or anything, just the 5 last messages sent. So if a user has 5 'templates messages', and then decide to craft a new message, the message with the proper userId and oldest datetime gets deleted and the new one is inserted.

I know how I could do this with a trigger. I want to know is there some other way of handling this via table schema? Like setting that there can only be 5 duplicate userId's and that new inserts should be handled in such a FIFO way based on date?

Just curious, I get the feeling that there is (based on nothing), but if a trigger is the best way of accomplishing this, then that is fine as well.

mustaccio
  • 25,896
  • 22
  • 57
  • 72
bjk116
  • 225
  • 2
  • 9
  • 2
    No delete and insert. When user created 5 empty records are inserted for him. Each time when a template must be stored UPDATE ... ORDER BY altered_at ASC LIMIT 1 used. – Akina Sep 24 '19 at 16:56
  • Definitely a better method. Thanks. – bjk116 Sep 24 '19 at 17:06
  • 2
    Anytime it allows to increase the amount of templates per user (make proper copies of the most ancient record for each user) or to decrease it (delete proper amount of the most ancient records). – Akina Sep 24 '19 at 17:10
  • Another very valid reason. I'm glad I asked this question ha. – bjk116 Sep 24 '19 at 18:19
  • 1
    Another option (strictly DDL, no triggers) is to add a template_number column, restrict its values to only five (say integer, 1-5) and add a UNIQUE constraint on (user_id, template_number). See the solution to a similar problem here: https://dba.stackexchange.com/questions/9662/check-constraint-does-not-work/22019#22019 – ypercubeᵀᴹ Sep 24 '19 at 18:22
  • @ypercubeᵀᴹ While that may prevent additional records from being added it also means you have to account for handling that condition and determining which record should be overwritten unless you wan to update that key on all the other records – Joe W Sep 24 '19 at 18:58
  • Simple answer: Don't demand more than the very limited features that FKs provide. Do it in a Trigger or stored routine or your app. – Rick James Oct 06 '19 at 01:35

0 Answers0