Best way to store this data in the database


#1

i have bunch of item properties that need to be stored in the database.
the options for the properties are:

-unknown
-yes
-no

what do you think is the best way to store this data?

should i use a tinyint and only store 0, 1, 2 for the respective options
or just use a varchar(10) and store the name of the result?

what is the usual standard?


#2

I tend these days to always use the full descriptive words - memory
implications are small, but it is easier to directly display the value
than have code to expand it wherever it is used. It also tends to
make the code logic easier to follow.

tonypm


#3

On Jan 5, 3:22 pm, tonypm removed_email_address@domain.invalid wrote:

I tend these days to always use the full descriptive words -

Definitely not in a case like this. The set of three values here maps
perfectly to SQL-style 3-valued logic – so use it! Store this as a
boolean value.

A larger set of strings could be stored directly in a character field
in the database, but if the number is under 100 or so, I’d advise
creating a separate table and simply referring to the strings by
foreign key. This practice will avoid all sorts of data normalization
issues. I’d never dream of storing a small number of repeating
strings as literal text the way you’re suggesting.

memory
implications are small, but it is easier to directly display the value
than have code to expand it wherever it is used.

Not really – especially if the application should run in more than
one language.

It also tends to
make the code logic easier to follow.

Again, not really. You have to explicitly test for == ‘yes’ rather
than simply testing for truth or falsehood.

tonypm

Best,

Marnen Laibow-Koser
removed_email_address@domain.invalid
http://www.marnen.org