Hello and welcome to our community! Is this your first visit?
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
    Mar 2012
    Somewhere over the Rainbow
    Thanked 5 Times in 5 Posts

    What would you do?

    What do you do when someone has asked you to code the database columns in a way that doesnít make sense?

    Someone wants three buttons- yes, no, maybe.

    Instead of one column with "yes, no, maybe" as a set of choices, they want a column for each yes, for each no, and for each maybe.

    It ends up being 15 columns in all. I'm cringing.

    I think the one column way makes more sense, but I donít want to bite the proverbial hand. Any advice? Do you code for what the client wants or what is best for the database?

    Iím trying to think what the logic is- Iím guessing it has to do with wanting some sort of column count- how many yesses, how many nos, etc.

  2. #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    St. Catharines, Ontario Canada
    Thanked 156 Times in 147 Posts
    why do they need to worry about how the database is set up? What they should want is for their requirements for the front end to work and you of course normalize the database so that the queries are easier to work with and will scale easily.

    Maybe you need to ask them what their requirements are (i.e. what type of reports are they looking for) and you show them how you are able to pull those reports.

    People with some knowledge are usually dangerous when they have nothing to do with the back end.

  3. #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Thanked 4,947 Times in 4,908 Posts
    And besides...
    I’m guessing it has to do with wanting some sort of column count- how many yesses, how many nos, etc
    How is having them in separate columns any better than having them in one column?

    Assuming you used an ENUM column (just for example...if you prefer using a CHAR or INT column that would work as well) such as
    CREATE TABLE whatever (
        question1 ENUM('yes','no','maybe'),
        question2 ENUM('yes','no','maybe'),
        question3 ENUM('yes','no','maybe')
    Then you could count the answers to, say, question2 thus:
    SELECT SUM(IF(question2='yes',1,0)) AS q2Yes,
           SUM(IF(question2='no',1,0)) AS q2No,
           SUM(IF(question2='maybe',1,0)) AS q2Maybe
    FROM whatever
    Further, the huge advantage to this is that if they change their minds later and add a 4th choice (e.g., "n/a") then no columns need be added to the table. You could simply do
    ALTER TABLE whatever MODIFY COLUMN question2 ENUM('yes','no','maybe','n/a')
    Or if you used a CHAR or TINYINT column no changes at all are needed.
    Be yourself. No one else is as qualified.


Posting Permissions

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