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.