table with very much fields

alirezaok

i have data.

for any record i have very much fields.

most of fields are 1/0.

as exmpale for first record:

mike 1 0 1 0 1 1 0 1 0 0

one way is i create field for each 0 or 1 (for this example i must creat 11 fields)

other way is i create 2 fields. one filed is "name". second field is "options". in the "options" field 0 and 1 split with: ,

as expample:

mike 1,0,1,0,1,1,0,1,0,0

for this exapmle i need 2 field.

first question is : which way is better? is there other better way?

then when i use of second way. how i query of it?

as example i want to select records where "options" field is: x 1 x 0 x x 1 x x x

x is 0 or 1 and it is not importat in select query.

second question: how do i query when i use second way?

thanks

Bjom

second question: how do i query when i use second way?

This is indeed the question, and that is why you should not do it.

Way 1 is fine. Many fields would be 250 but certainly not 11....

Bjom

johanafm

I don't agree with Bjorn, probably for the first time. Using varchar or text for options is out of the question, but you could use the SET data type for the second column, and if you want to retrieve columns matching options 0, 1, 1, 0:

WHERE options = 6

Moreover, if you need to find anything where bits 2 and 3 are set to 1, and don't care about anything else

WHERE options & 6

Bjom

A bit mask. How cute 🙂

I didn't know about the set data type to be quite honest. Thx for pointing me there. It's a possibility, could be good, however it needs careful consideration.

Bjom