sql - Database design: Better to store more columns or use a single column with a dictionary on Backend? -

i'm building web app takes preferences , saves them.

the dataset want save consist of unique id, search string , finite list of parameters represented true or false. list of parameters 10 in number.

i haven't decided type of database i'm using assuming has rows , columns, more efficient have id, search string , parameters separate columns or more efficient have id, search string , single column representing parameters using sort of dictionary translate on end.

for example represent option a, c , d a-c-d in single column , use dictionary on retrieval work in application. or else using cola: true, colb: false, colc: true, cold: true, ..., coln in table , working when pull through

would more useful choose sql style db on mongodb in either case?

the answer depends. normally, 1 uses relational databases store relational information. mean have separate columns options , values. there traditionally 2 ways of doing this.

the common normalized form, each option has column in users table. key user id , can read values. works when there finite list of options doesn't change much. useful when want query table options -- users have particular option, instance.

another method called entity-attribute-value (eav). in method, useroptions table have separate row each user , each option. key consist of user/option pair (and option might id references master list of options). flexible; easy add values , can handle unlimited number of options per user. downside getting options user can cumbersome; there no data type validation on values; implementing check constraints validate values tricky.

a third method can useful purposes. store options in single "string" -- more typically, json object. useful when using database acid properties , don't need query individual options. can read "options object" application, , parses them options.

and, these 3 examples of methods of solving problem. there hybrid approaches combine elements more 1 solution.

which solution works best depends on application. if have handful of predetermined options, go first suggestion, single column per option in table.