Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Jan 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Best practice question - checkbox values into multiple database columns or just one?

    Hello,

    Can you please help me clear up something? When you have a range of checkbox options for one form question, is it generally considered better programming to place the checkbox responses strung together into one database column with a delimiter, or to have a separate column for each option? Why might someone choose to put the responses into separate columns?

    I appreciate your insight.

    Thanks,

    Steve

  • #2
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,627
    Thanks
    0
    Thanked 648 Times in 638 Posts
    You should never store multiple values in a single column in a database. That is one of the first things that you split out into separate records when normalising a database.

    The best alternative is to have one column for the values but have a separate record for each. That way you can add another hundred c heckboxes to the form and not have to change the database structure.

    If it is 100% guaranteed that the checkboxes cover all possible options and that they will never need to change then you could set up a separate column for each.

    The main reason for never placing multiple values in one field in a database is that it does away with your ability to make simple queries on the database to extract information. It also makes defining the size of the field difficult. As a separate column you only need to store a single value of (eg. 0 or 1) to identify what the value is. With multiple values in the one field you don't know how big a field you will need to hold all the values (particularly if you need to add a few more values later).
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #3
    New Coder
    Join Date
    Oct 2013
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You should never store multiple values in a single column in a database. That is one of the first things that you split out into separate records when normalising a database.
    In a traditional relational database: yes. Create an EAV structure and store each checkbox as a separate row.
    And yes that means you will get *lots* of records and no that doesn't really matter, databases are designed to be fast a set-oriented operations, in fact they much faster at working with lots of smaller rows than ad fewer larger rows.

    If it is 100% guaranteed that the checkboxes cover all possible options and that they will never need to change then you could set up a separate column for each.
    With the emphasis on *guaranteed*, because in reality people use the words "it will never change" when they realy mean "I don't know if it's going to change, stop asking difficult questions and get on with it!"

    The main reason for never placing multiple values in one field in a database is that it does away with your ability to make simple queries on the database to extract information.
    That's true if you store the data as CSV data, but with current document-based technology it's slowly becoming a option to use things like XML and JSON to store the options, while still enabling the relational functions like integrity checks and fast/easy searches. Think about PostgreSQL's HSTORE and the more recent JSON abilities, they use one column and have indexable values that you can interrogate using extraction functions without any effort at all.

    But that's something for a rainy sunday afternoon, for now you should use the classic normalization.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •