0

I'm creating a small questionnaire, with below database design; following the schema from this thread made by Michael Durrant, with slight modification.

Now, for some questions, I must provide cities as options of answers; which already there in master_cities table. Eg: Which city do you reside now?

Answer will be in dropdown format which derived from a master_city table. I shouldn't copy the whole content of master_cities_table into option_choices table, should I???

Any ideas are greatly appreciated.

current_db_schema

Edit: I'd like to clarify the question.

  • This question is totally unrelated with UI/UX, pure db design.
  • The goal I'm trying to achieve is to avoid data redundancies such as copying master table records into options_choices.
  • Since it's a survey system, all the choices of answers must be database-driven, like A. Strongly agree, B. Neutral, C. Totally disagree. Which could be reused to answer other questions, like "Where were you born?" or "Which cities have you lived before?"
  • Master_cities table here is only one of the master tables (and the biggest) that I need to refer to provide choices of answers.

Hope that clears the confusion.

Current approach:

  1. I removed the referral_table field in options_choices table and put it inside the options_group table instead,
  2. set option_choice_id in answers table as NULL-able,
  3. and save the master_cities primary key as text in answers_text field.

That seems to work.
But, there maybe a better approach out there, so you're welcome to share your insights, oh db experts!

Community
  • 1
  • 1
NineAllexis
  • 52
  • 5

1 Answers1

0

Don't know. Usability would suggest that too long a drop down list won't be a good user experience.

Another approach would be to try type ahead, similar to what Google does when you start typing terms in the search text box, to look up city names. Once the user types a letter into the text box, you can narrow the query down and only present city names that match.

duffymo
  • 305,152
  • 44
  • 369
  • 561
  • Thanks duffymo. thats a great idea. however how would you store the value? as text? i cant put option_choice_id Foreign key as NULL... – NineAllexis Oct 03 '13 at 01:43
  • Yes, text. What else would a city name be stored as? I have no idea why you need a foreign key. It's SELECT * FROM MASTER_CITY WHERE CITY_NAME like 'search%'. Gotta have a search string to start. (Table names should never be plural. It's MASTER_CITY table with multiple rows.) – duffymo Oct 03 '13 at 09:20
  • Please, I know how to query my db. But, I'm a bit confused, are you saying that I should bypass the option_choices table? – NineAllexis Oct 03 '13 at 10:49
  • You may or may not know how to query your DB. You said you want to populate a drop down from a table of city names. I don't see what your option choices has to do with that. I'm being as direct as possible: query the city table. – duffymo Oct 03 '13 at 11:21