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
    Mar 2012
    Location
    Somewhere over the Rainbow
    Posts
    96
    Thanks
    7
    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
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 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
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,869
    Thanks
    79
    Thanked 4,421 Times in 4,386 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
    Code:
    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:
    Code:
    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
    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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