jeudi 2 avril 2020

How should checklist be stored in the database from atomic point of view?

I have 15+ years of experience in software development, but it was only recently that I had to come across saving checklist in database and I was a bit lost. Then I decided to save it in one column, as comma separated values and it worked great. But now I am implementing the same in a different project and want to know what is best way to implement it from database point of view. Take this as an example enter image description here

This is an example of one-to-many relationship. A single user may like different cars. The best approach from database point of view in normalized form is

id check_id check_name

The problem is this will require seperate SQL queries and saving and change options would become more complex (would need deleted addition rather than simple update). Note that from database best practice point of view, we want to save only atomic data, so saving comma separated values in on column is not recommended.

My question is,

  1. what is the best practice to implement checkboxes in database?
  2. Is comma separated values better or a separate table for options? Does one provide more flexiblity down the road (eg. we want to add more companaies in option)
  3. Is there any performance difference? What the normalized design show any enhancement, such as in search etc, that the other one does not have?



Aucun commentaire:

Enregistrer un commentaire