1

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.

1

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).

  • It is purely for reporting for people further up the food chain. There can only be one selection in each column, using the cars as an example a car is either new or second hand, a sales man might try and sell you a 'demo' model but we all know that is a car the salesman has used for a few months so it's a used car. I was just thinking that having 5 tables with 2 to 4 rows in them was maybe not the best option. – Rob Jan 22 '16 at 17:06
  • I think I will stick with multiple tables, having looked at 'check restraints' I think that opens up opportunities for bugs where the users software and the database get out of sync. – Rob Jan 22 '16 at 17:12
  • Well, Check Constraints restricts the inserted data to exactly what is specified. However, it does not give any easy way of 'extracting' the possible combinations that the column accepts. Multiple tables is a good extension when you want to store more information about the actual choices. – HansLindgren Jan 23 '16 at 11:39
  • One thing that can be kept in mind, when dealing with boolean values (new car vs used car) is that you can use multiple columns (IsNew and IsUsed for example (and again, preferably a check constraint to restrict that the car cannot both be new and used at the same time)). This is very efficiently stored (no long text strings repeating through every row but does neither give the possibility of easily extracting, for reporting, the possible options available). – HansLindgren Jan 23 '16 at 11:42
  • One caveat is to stay away from (even though, in some cases, easily comes to mind) is to a generic key-value table where all possible selections are stored and multiple columns have FKs to this MUCK. Easy to design but is horrible and will come back to bite the designer in the ass for many many different reasons (simple-talk.com/sql/database-administration/…) – HansLindgren Jan 23 '16 at 11:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.