Say you have a column in a search table by the name of politics.
Each political view is represented as an integer from 0 - 16. A user can
choose multiple political views, and those views have to be represented
in the db as a single row. How can a single column for a single row of a
searched table represent multiple values while using an index?
Current I convert the integer value 0-16 into a bitwise representation.
So if a user picks values 2,5 the integer entered into the db, for the
users row for column politics as
18 (this is the result of the bitwise manipulation)
Thus when searching for a users politics and you want users who have
political views of 2,5,7, or 9 show up in your search a query needs to
be done in this manner using the approach above.
SELECT * from searched_table where politics & 338;
What is the problem here?
An index is not available when using bitwise arithmetic on tables so a
resulting table scan occurs.
So, my question is how can multiple values for a single field get
represented in a single row and receive the benefits of an index? How
can this data be represented?
