Results 1 to 5 of 5

Thread: design advice

  1. #1
    Join Date
    Nov 2002
    Posts
    98

    Unanswered: design advice

    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


    TIA

    Mark

  2. #2
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    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.

  3. #3
    Join Date
    Nov 2002
    Posts
    98
    can you tell me how to encrypt the flags into binary form? eg. what datatype to use and/or what function to encrypt.

    the minimum space requirement for VARCHAR2 and NUMBER are all one byte, if i remember correctly.

    Mark

  4. #4
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    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.

    An example:

    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.

    Hope this helps in your decision and design.

  5. #5
    Join Date
    Nov 2002
    Posts
    98
    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.

    perhaps someone can offer me some suggestion?

    Mark

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •