mysql – How to organize the structure of the database

Question:

Introductory:

There is an sms table that contains data about the pending sms. There is also a text_patterns table that stores text patterns prepared by the user. The sms table has a pattern_id field – a link to a text template

The user uploads a csv file to the system. In the csv file there is a list to which number which text template to send. 2 columns. Number, template id.

Now all this is processed and working

Task:

We want to add a feature so that in the second column you can write not only an existing text template, but also the text itself to be sent. Those. I will look at what is in this field – if it is a number, then this is a text template, and if it is a string, then some new processing logic.

I can't figure out how to store this data in the database.

Solution options:

  1. If text, then create a new text pattern in the text_patterns table and continue to work according to the old scheme. I don't like the solution, because templates can be very unique and each number will have its own separate template. The template logic is missing.

  2. Add a text field to the sms table in which to store the value. those. add the sms_text field and the link to the test pattern is_pattern (0|1). I don't like the solution. 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 queries all the time instead of one. One for sms with templates, the other for sms with custom text.

Maybe there are some more beautiful solutions?

Answer:

Your second option is similar to the truth, only it needs to be completed a little. We need two fields: template id and template text, both fields allow NULL values. template-id must be a foraign key pointing to the table with templates. Do not be afraid of NULL values, the DBMS does not actually store them, for example, MySQL stores one bit in the record header for such fields, indicating whether the field exists or not, other DBMSs act in much the same way. There is no need for any indication of whether there is a "unique template" in the record, the very fact of the presence of text in the sms_text field is a sign. At the trigger level, you can prevent the insertion of records where both fields are filled or none of them are filled.

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

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

We join tables by LEFT JOIN , so if the pattern_id field is 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 return the first of the listed fields that is NOT NULL, i.e. if the template id is set and an entry in the templates is found, then the text will be taken from there, if the template is not set, then the text will be taken from the sms_text field in the sms table entry itself. Perhaps it is worth rearranging the arguments, then the text from the sms itself will take precedence over that found in the templates if both fields are filled.

Post Views: 7
Scroll to Top