How would I design this simple schema?

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 |

Answers:

id | question_id | answer_text |user_id | created_at | status

Categories:

id | category_text | desc

Status:

id | status_text | desc

Users:

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.

Regards,

John
LVL 1
John S.Web DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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 dbaCommented:
you might be able to laugh back at your co-worker ... when asking about the performance
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.  

Jim.
SolarWinds® Network Configuration Manager (NCM)

SolarWinds Network Configuration Manager brings structure and peace of mind to configuration management. Bulk config deployment, automatic backups, change detection, vulnerability assessments, and config change templates reduce the time needed for repetitive tasks.

Scott PletcherSenior DBACommented:
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.

MOST TABLES SHOULD NOT BE CLUSTERED BY AN IDENTITY COLUMN!

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
text
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
description

Statuses: table key = status id
status id
status code (short description)
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
description

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John S.Web DeveloperAuthor Commented:
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.
slightwv (䄆 Netminder) Commented:
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?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.