I am looking for advices on how to handle multiple choices in a column?
By way of example lets say selecting a fruit for lunch. Options are Apple or Pear. And you are 100% sure those two options will always be available, but you can only be 95% sure that other fruit won't be added to the list, but can be sure that if they are there will never be more than 3 or 4 types ever, and once added they will never be removed.
Then let's say you have another similar column for sandwich types, and another for drink types with the same requirements for their types.
Is it still best to create a table for fruits, another for sandwiches and another for drinks?
What about if there really will only ever be 2 options, like new car or second hand car but you want to use the descriptions, not a boolean value so other staff can drag and drop a quick query in MS Access or paste it into a spreadsheet?
If it matters the DB is ms SLQ Server, and there will be about 1500-2000 new rows per month in this table, but queries sometimes going back over several years of data. Currently have about 5 years of data to insert into this table when complete.
Best Answer
Well, it is always difficult to give a generic answer to design questions. Best would be to study the normal forms and see how that can be applied to exactly your situation! :)
Having said that, it sounds like you want to store reporting data (OLAP type table/database) so I base it on that. In OLAP, you can see all different types of items which can be selected 0 or 1 time as Attributes to whatever you like to consider the 'Fact' and can be stored in the 'Fact' table. You can opt to not use separate tables for your fruit, sandwiches or drinks but only have 'check constraints' in your Fact table columns.
However, if your requirement is a little bit more complex, I'd opt for one Fact table, one for fruit (where you can store more lookup data per fruit), one for sandwich (with the same requirements as for fruit but possibly a different set of attributes/columns) and one for drinks. I.e. One extra table per 'selectable attribute'. Now, if someone can choose More then 1 of anything, it gets more complex and you need to consider other options (link tables or such).