Any good method to store Multiple Selection Value in database?

Question

Hi there,

What I am currently know is that use bitwise and Int type to store multiple selection value in Database.

Take Sql Server 2008 as a example, 'Int' type in sql server is 32bit, so it accepts 32 answer, I only can use 1,2,4,8,16, etc to represent the answer, due to I need to store the multiple selection into one value, and use bitwise operation to separate them.

Int: -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) BigInt: -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)

No mater Int or BigInt, the number of answer is till have a limit (32 or 64 or 128).

So is there any other way to deal this situation?

Many thanks.

Answer 1

It's probably a lot easier to store these using the bit data type in SQL Server. MSSQL will compress multiple bit columns into bytes containing up to 8 bits. For example, if you have 9 bit columns, it will take up 2 bytes total.

But if you have a really large number of bits, then it will just make for poor database design to break them out into separate columns in which case perhaps you can use a fixed-length binary data type which you can access as a byte array in .NET or VB.

Answer 2

Using bit fields as Josh suggests will work, but you can still in theory reach column limits.

An alternative would be to have a separate table that has a row for each combination of answers. Each row would contain a unique ID, and a varchar column with a list of answers using whatever method you need to identify them - e.g a comma separated list of answer IDs.

When inserting/updating the main table, first look for a row in the answer table that matches the selections. If found you have the ID, otherwise insert the new combination and grab the new ID. Store the ID in the main table.

Answer 3

I suggest for a larger amount of potential selections you create a separate table with a foreign key back to the original table.

e.g.

Table Customer

customer_id    customer_name
-----------    -------------
          1             Fred
          2           Barney

Table Customer_Options

  option_id   customer_id    option_value
  ---------   -----------    ------------
          1             1               1
          2             1               1
          1             2               0
          2             2               1

The type of option_value is bit but could be a varchar or whatever you need. This way the selectable options can be increased without a massive schema change.