How would I design this simple schema?

John S.
John S. used Ask the Experts™
We are designing a mobile app that will have a question and answer section, very similar to EE as a matter of fact.

I need some help with the database design.

I had something like this in mind, please don't laugh:

Questions Table:

id | category_id | question_text | status_id | user_id | created_at | views |


id | question_id | answer_text |user_id | created_at | status


id | category_text | desc


id | status_text | desc


id | username | nickname

Of course the tables will have more fields, but that would be the general way I would do something like this.

My Problem:

The reason I am posting here is because the last time I showed a coworker my table design for a project similar to this, he damn near laughed. He explained to me somewhat, that I needed to use attributes and that by doing so all the related tables would be unnecessary.

But he didn't elaborate. Attributes? I think he meant like using JSON in the field values or something. At least, that's what I got out of his rambling.

So can someone show me a professional, clean, efficient, modern, and functional approach to the above table design? Can this be done by using JSON as data with field names, such as my coworker suggested ( again, I think ).

Thanks! Any example would help.


Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

For concrete help in such a modelling issue, we need to know your requirements. Then we can discuss your design. But for a questionnaire, you need at least also a questionnaire entity, otherwise you would only have stand-alone questions.

When I need to speculate about that "attributes": I guess he meant attributes like in Entity-Attribute-Value model.
But this model has some serious drawbacks, thus while being flexible, it is only used in a handful of scenarios. In most cases you don't need it.
Geert GOracle dba
Top Expert 2009

you might be able to laugh back at your co-worker ... when asking about the performance
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Ditto everything ste5an said.

and on EAV's, they do have their place, but in general are used rarely.    They do work well when used properly.   You just need to be aware of the drawbacks.  

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Senior DBA
Most Valuable Expert 2018
Top Expert 2014
You ought to take a step back and do a data design before doing a database (table) design.  But that never happens with developers, so forget I said that.

At least stop making the stupid mistake of automating adding an identity to every table and clustering on it.


Particularly intersection tables are often far better clustered by the combination of the intersection keys.  For example, an order_items table is better keyed by ( order_id, order_item_id ) than order_item_id alone.  This may be more or less than you need, since we don't know any details of what you're actually trying to do, but it should at least give you some things to think about.

At any rate, here's a quick mock up to help you get started.  I didn't bother adding the _s to some names yet: in the early design process, it's easier to use just a descriptive name.  You can add the _s when you convert to actual column names and data types.

Questions: table key = question id
question id
status id (? I'm not sure what this is)
category id
create user id
create date

Question_Answers: table key = ( question_id, answer_id )
answer id (constraint = unique across all qs)
is correct (may be valid for some qs and not others)
degree of correctness (may be valid for some qs and not others)
create date
creator user id
status id (? I'm not sure what this is)

Categories: table key = category id
category id

Statuses: table key = status id
status id
status code (short description)

Users: table key = user id
user id
user name
user preferred name

questionnaires: table key = questionnaire id
questionnaire id
question order
question id
create date
create user id

questionnaire_Users: table key = questionnaire user id
questionnaire user id
questionnaire id
user id
when taken (datetime)
questionnaire reason id

questionnaire reasons: table key = questionnaire reason id
questionnaire reason id
John S.Web Developer


Thank you all for taking the time to respond. It looks like I have a little work to do, but now I have a good starting reference.
Most Valuable Expert 2012
Distinguished Expert 2018

I would agree with normalizing the logical long before doing the physical.

Pay attention to Many-to-Many situations.

For example:  This question appears in four Topic Areas.  I assume this is your "Category".  Does your design allow for this?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial