Introductory:

There is an sms table which contains data about waiting for sending sms. There is also a text_patterns table that stores text patterns prepared by the user. In the sms table there is a field pattern_id - connection with a text pattern

The user loads the csv file into the system. In the csv file, the list to which number which text template to send. 2 columns. Number id of the template.

Now it's all processed and working.

Task:

We want to add a feature so that in the second column it was possible to write not only the existing text template, but also the text to be sent. Those. I will see what is in this field - if it is a digit, then this is a text pattern, and if the string is some kind of new processing logic.

I just can not understand how I store this data in the database.

Solution options:

  1. If the text, then create a new text template in the text_patterns table and continue to work according to the old scheme. The decision does not like, because Templates can be very unique and you will have a separate template for each number. The template logic disappears.

  2. Add to the sms table a text field in which to store the value. those. add the sms_text field and link to the is_pattern test pattern (0 | 1). The decision does not like because we will have empty fields on each line, either pattern_id or sms_text . Also, for further data processing, I will need to write 2 requests instead of one all the time. One for sms with templates, another for sms with custom text.

Maybe there are some more beautiful solutions?

    1 answer 1

    Your second option is similar to the truth, only it needs a little finish. Two fields are needed: the template id and template text, both fields allow null values. The id of the template should be a foraign key pointing to the table with the templates. Do not be intimidated by NULL values, the DBMS does not actually store them, for example, MySQL for such fields stores one bit in the header of the record indicating whether the field is there or not, other DBMS act in approximately the same way. No sign is a "unique template" or not, in the record is not needed, the mere presence of text in the sms_text field is a sign. At the level of triggers, you can prevent insertion of records where both fields are filled or none of them are filled.

    Just to get the current template, two requests are not needed, everything is solved in one request:

     select ..., coalesce(T.text,S.sms_text) from sms S left join text_patterns T on T.id=S.pattern_id

    We combine tables by LEFT JOIN , thus, if the pattern_id field pattern_id empty, then the query will still return the record, only in it the text from the template table will be NULL. The coalesce() function will give the first of the listed fields, which is NOT NULL, i.e. if the id-template is specified and an entry in the templates is found, then the text will be taken from there, if the template is not specified, then the text will be taken from the sms_text field in the sms table entry itself. It may be worth relocating the arguments, then the text from the sms itself will take precedence over that found in the templates if both fields are filled.

    • Thank. I read your answer and thought that it does not suit me, because I have through the text template further goes the connection of sms and the server to which to send the request. But then I thought I also understood that you are right, but I need to organize the connection differently. Thank you very much. - Rochfort