Need advice on best way to store multiple selections

[PSI]

My application presents several lists of user defined items from which
a user can later select any number to be active (eg a pick list ). I
need to track the items picked for each user. The list will generally
be approx 60 items.

The items are stored in a table of (ID, ItemName). I'm looking for
suggestions on efficiently storing and retrieving the items picked. In
particular how can one create efficient queries to check whether a
particular item or set of items is picked?

The ID is autonumber but I had thought to make it a number where I
would generate it to used as a bitmask correponding to the index
(0x01,0x02,0x04 etc) but don't know if this is feasible beyond 32
items.

Along the same thought lines, I was also considering generating a "bit
string" ( where each index corresponds to (0==not picked, 1==picked)
) in a field of the user record. (I suppose the "bit string" could be
a text string, a bunch of longs or a binary blob).


Although I've been programming for a lot a years I'm a novice at
database/access/SQL so any quidance would be very much appreciated.

Thanks
Frank

 

[Allen Browne]

Without question, the best way to store multiple selections is to ceate
another table. Related it many to one, so one record in the main table can
have any number of records in the related table. From there, it's dead easy
to query the selections.

While it is possible to create bit fields, they cannot generally be
justified based on the reduction in disk space now you can buy many, many
gigabytes for $100. The time required to work this bitfields would need to
be justified on some other basis.

 

[PSI]

Am I correct in understanding that the new table would have just one
field representing the ID of an item selected with multiple records
representing the set of choices for each user?

OR

the table would have a field ( Yes/No ) for each possible choice and
there would be one record for the set of choices, in which case the
table would have to be expanded whenever a new Item was added.

Thanks
Frank

 

[Allen Browne]

Your first choice: multiple records.

It's important to have all the values in one column. Makes it so much easier
to query.