we are designing a schema which invloves a lot of fields with only on/off flags. I was wondering if there is a way to minimize the storage space used in these fields. for example, storing it in a 1 bit field instead of one byte
You could encrypt the flags into binary as you suggest, and this would save a small amount of space. However, this space saving is not so very significant unless you have an extraordinarily large number of records. The downside of encrypting the flags into binary is that you complicate any where clauses you need to write.
It is a trade off between number of columns against complication. It all depends on your exact situation.
You have to assign each of your flags to a binary digit, then convert this to a number. Decrypting is the reverse, you convert the number back to a binary to see what each of the flags are.
Say you have 5 flags, each for a different process, and we label these a, b, c, d, e
Assign each of these to a part of the binary number, it is conventional to read these right to left.
I will label the binary number parts as b1 for first digit, b2 for second digit etc. So we have
b5 b4 b3 b2 b1
e d c b a
Now if we have flags set as
a= on, b=off, c=on, d=off, e=off
We have a binary number of: 00101
Which converts to the number 5. Which is the number you would then store. Later you would retrieve this number and convert it back into binary to see the indivual flags.
If the flags were set as
a= off, b=on, c=off, d=on, e=on
The number would be 26.
As you can see this involves a lot of conversion to and fro, so you really have to be very short of space to make this worthwhile. You cannot test a flag in a where clause without first converting the number stored to binary and retrieving the flag.
In the past I have worked on very small hand held instruments with little available memory etc and this sort of encapuslation was worthwhile. However now working with a large Oracle database it is not so worthwhile. As I said before it all depends on your requirements.
thanks for your reply
i thought there might be a datatype that stores bit information in oracle
apparently i was mistaken.
the reason i am considering encoding it into binary is there might be about 150 flags for each entity, and i am hoping to come up with a design that can minimize space usage while being relatively efficient.
so far i am thinking either just put every flag into a huge record
or separate each flag into a record, and use a template id to group the flags together, but the concern i have for the first idea is whether is a good rdb design( i always think a huge record is not the way to go), and the concern for the 2nd idea is the number of record will increase 150 times for each template, which might be a problem with query efficiency.