best way to store options in a db

Question

Hi there,

i have a table and one of the columns is co_com

this is communication preferences

there are three options (and only ever will be) i dont want to have a seperate column for these values

so i was thinking of storing them as

sms/email/fax sms = yes email = no fax = yes which would be stored as: 101

but, im thinking thats not the best way

what other ways can you see?

yes i am aware that this is a subjective question

but im not sure how else to ask.

Answer 1

Storing combinations of logical values as coded binary is... 1900's. Seriously, how much does disk space cost these days, and how much do you save by cramming three bits of information into a single number rather than three bytes or characters?

Go on, create three columns with sensible names, and store either 0's and 1's in them, or if your DB is weird that way, story 'Y' and 'N'. But don't do this binary cleverness stuff. It will bite you eventually when you try to write sensible queries.

Answer 2

You're correct. That is in fact not the best way.

You say you don't want to have separate columns for these values, but that's exactly what you should be doing.

Answer 3

In my mind, columns is the best way to go, for ease of use if nothing else. The columns are straight forward and won't be confusing in the future. BUT I wouldn't say storing them in a single column as 3 digits is necessarily bad, just confusing. Save yourself the headaches later and do 3 columns.

Answer 4

Another point of view would be to have another table called com_options for example. Have an ID field and an options field, store all of the different communication options combinations in the options field along with a unique ID in the ID field and in your co_com table have an opt_id field referencing the ID in the com_options table. Then use an INNER JOIN to join these 2 tables together.

Answer 5

If your DB is MySQL, then you can use SET datatype.

It's OK, don't worry -- sometimes we should denormalize tables :)

But if your DB isn't MySQL, then you also can use this method, but implementation will be non-your-DB-native. Also bitwise logic on a big bunch of data works very well vs default normalize d one-to-many relation. Because it`s more computer-oriented.