Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
Thread: What would you do?
04-24-2012, 03:00 PM #1
- 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.
04-24-2012, 05:35 PM #2
- Join Date
- Mar 2006
- St. Catharines, Ontario Canada
- 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.
04-24-2012, 09:19 PM #3
How is having them in separate columns any better than having them in one column?I’m guessing it has to do with wanting some sort of column count- how many yesses, how many nos, etc
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
Then you could count the answers to, say, question2 thus:Code:CREATE TABLE whatever ( question1 ENUM('yes','no','maybe'), question2 ENUM('yes','no','maybe'), question3 ENUM('yes','no','maybe') ... );
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 doCode: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
Or if you used a CHAR or TINYINT column no changes at all are needed.Code:ALTER TABLE whatever MODIFY COLUMN question2 ENUM('yes','no','maybe','n/a')
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.