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.