mardi 2 décembre 2014

Best practices for storing a small list of booleans


For a new application, I am designing a schema in MySQL to hold the data. It manages document information, with many optional attributes. Among them are lists of 3 to 5 boolean values (checkbox lists in the UI) representing steps taken by the user.


As of now, they are stored in the main document table, with columns looking like this (stored as BIT(1)):


process1_step_step1

process1_step_step2

process1_step_step3

process1_step_step4


process2_step_step1

process2_step_step2

process2_step_step3

process2_step_step4


process3_step_step1

process3_step_step2

process3_step_step3


Of course, it is with descriptive process and step names. Whatever step is followed is set as true. However, for some documents, none of these will ever need to be set, as they are dependent on another value being true. For such documents, all these columns are a waste of space. On the other hand, it is unlikely that more steps would need to be added, so an extra table also seems redundant, and overkill for such small lists.


Is there an alternative solution, and if not, what is the favoured option?





Aucun commentaire:

Enregistrer un commentaire