4 Replies - 6967 Views - Last Post: 22 December 2012 - 08:46 PM Rate Topic: -----

#1 sas1ni69  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 85
  • View blog
  • Posts: 431
  • Joined: 04-December 08

Dynamic Database Content Design

Posted 20 December 2012 - 07:05 PM

Hi,

'm currently working on a survey system(PHP/MySQL) where the content is dynamically generated. Users can create their own questions and other users can answer these forms.


How do I go about designing the database? What is the best approach? Do I save each question in an individual row? Do I create a text field and populate it with long string with delimiters then explode it? Same goes for the response. It is estimated that there is going to be several thousand surveys and I can only imagine that amount multiplied for responses.

I'll give you an example to illustrate my point a little clearer :)/>



Survey Table
id
name
date

survey_question table
id
survey_id
question
type
date

survey_user_answer
id
user_id
question_id
answer
date




Should I store it in this manner, where every response gets a field? Eg:

Survey Table
1    user survey    21/12/2012
2    marketing survey    22/12/2012

survey_question table
1    1    how did you feel about the user?    subjective    21/12/2012
2    1    how do you think the user did?    subjective    21/12/2012
3    1    where should the user got today?    subjective    21/12/2012
4    2    what did you think of the marketing?    mcq    21/12/2012

survey_user_answer
1      1    1    the user was not reponsive    21/12/2012
2      1    2    the user didn't do well    21/12/2012
3      1    3    the user should go to  DIC  21/12/2012
4      1    4    the marketing was too obvious   21/12/2012
5      2    1    the user was not reponsive    21/12/2012
6      2    2    the user didn't do well    21/12/2012
7      2    3    the user should go to  DIC  21/12/2012
8      2    4    the marketing was too obvious   21/12/2012
9      3    1    the user was not reponsive    21/12/2012
10     3    2    the user didn't do well    21/12/2012
11     3    3    the user should go to  DIC  21/12/2012
12     3    4    the marketing was too obvious   21/12/2012




OR:


Survey Table
id
name
questions
date

survey_user_answer
id
user_id
survey_id
answer
date




Should I store it in this manner, where every response or survey is stored in a single row and put in a dilimiter? Eg:

Survey Table
1    user survey    1%subjective%how did you feel about the user?@2%subjective%how do you think the user did?@3%subjective%where should the user got today? 21/12/2012
2    marketing survey    1%mcq%what did you think of the marketing 22/12/2012


survey_user_answer
1    1    1    1%the user was not responsive@2%the user didn't do well@3%the user should go to DIC    21/12/2012
2    2    1    1%the user was not responsive@2%the user didn't do well@3%the user should go to DIC    21/12/2012
3    1    2    1%the marketing was too obvious    21/12/2012




What about storing it as XML? Does that work? Could anyone please provide an opinion or point me in the right direction.

Pros? Cons? I just need advice :D

Thanks in advanced!

Is This A Good Question/Topic? 0
  • +

Replies To: Dynamic Database Content Design

#2 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3730
  • View blog
  • Posts: 6,017
  • Joined: 08-June 10

Re: Dynamic Database Content Design

Posted 20 December 2012 - 09:45 PM

View Postsas1ni69, on 21 December 2012 - 02:05 AM, said:

Do I create a text field and populate it with long string with delimiters then explode it?

No, definitely not. This is one of the most serious design errors you can make in a RDBMS database. It violates the 1NF (1st "normal form", the first "rule" of RDBMS design), which is basically that you never save more than one piece of data in a field, and you don't create multiple identical columns. (Like "phone1", "phone2", etc...) This includes storing any type of formatted data file, like XML, JSON or CSV. Those are all multiple pieces of data, packed into one string. - Violating this rule can have serious performance implications, not to mention that it complicates the application code unnecessarily.

I explain these normalization steps in this tutorial, if you want to explore that further. (Which I recommend, if you plan on designing MySQL databases.)


What I would suggest you do in your situation is something more like this:

Attached Image

There each survey has a user assigned as the owner. Surveys can have multiple questions, each question having as many options as are required. The questions can have different types, like "text", "radio", "checkbox" (thinking in HTML form types here.) Then each user can answer each question, selecting one or more of the options for each question. You don't really have to link each user to the surveys, as you can determine that by the questions they've answered. (You can though, if you want. There may be a need for it in certain circumstances, if a performance boost on those queries are needed. Won't be hard to add after the fact though, so I'd worry about it later.)

The only real difficulty there is the question types. Like, say you have a "text" type, where the option needs a written answer. Obviously you couldn't use the Option table to store that; it's user dependent. For this case in particular, I've added a nullable "text" field in the AnswerOptions table, where written answers could be stored. This, however, would not be "enforced" by the database itself, it would only facilitate the ability to store the data. The business logic for that would either have to be written into the database as a procedure, or taken care of by the application code. From a database integrity perspective, you'd probably want to prefer a stored procedure, but writing that in PHP is also an option.
Was This Post Helpful? 1
  • +
  • -

#3 sas1ni69  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 85
  • View blog
  • Posts: 431
  • Joined: 04-December 08

Re: Dynamic Database Content Design

Posted 20 December 2012 - 11:50 PM

Hi Atli

You cannot imagine how grateful I am for that response. It's really comprehensive and you insightfully understood my position and explained in great detail. I just read your tutorial twice through and I only can tell you that I now know so much more than 3 hours ago.

I have another question though :D

Following this design, I am only afraid that the database will quickly exponentiate and might slow the application down. In a typical environment, what is still a maintainable amount of data? What is the size of an "acceptable" database. I cannot really imagine having more than a few thousands.

Could you maybe just point out how much data is still maintainable?


Thank you so much once again. I truly appreciate your help.
Was This Post Helpful? 0
  • +
  • -

#4 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3730
  • View blog
  • Posts: 6,017
  • Joined: 08-June 10

Re: Dynamic Database Content Design

Posted 21 December 2012 - 06:21 PM

There are a lot of things involved in determining answers to such questions. In a normalized database, it takes a whole lot of data to get to a point where it starts causing performance issues. Of course, for databases of a certain size, keeping the performance at a usable level requires some optimizations. Those can involve things like creating optimal indexes for slow running queries, rewriting queries to be less demanding, or manipulating the server configuration to allow the server more room to operate. - The performance of large databases aren't necessarily about the amount of data, but more about how queries are set up and how the server is configured. You can easily maintain databases with gigabytes of data by just configuring it correctly and making sure your queries aren't abusive.

One common issue for inexperienced designers is lack of proper indexes. I've seen a single index drop the speed of a demanding query from several minutes to a couple of hundred milliseconds. (And I've heard far more drastic tales from others.) - Another thing that tends to be an issue is the configuration of the server's caching functionality. By default, MySQL's InnoDB query pool is only 8MB, which means that MySQL will only keep 8MB of commonly used query data in memory at a time. With a setting like that, most of the data being queried will have to be read from the hard-drive. Unless your database is exceeding several gigabytes in size, the server can easily be configured so that it can keep most, if not all, of the InnoDB tables in memory indefinitely, which can significantly improve query speeds. (And similar things are possible for MyISAM as well.)


Just for kicks, I set up the database design I posted earlier, and inserted a bunch of random test data into it. With over 100,000 surveys, ~600,000 questions and over ~2.1 million options, where three users provided answers to all questions that didn't belong to them (which comes to ~1.2 million answers and answer options total), the query performance is perfectly fine, with barely any noticeable drop from when the database was practically empty. And that's using MySQL's default configurations and only the obvious indexes indicated by the table design itself.

As an example, a query like this, which spans all six major tables in the database, is executing in a few hundred milliseconds, dropping down to a few milliseconds once the InnoDB query pool kicks in.
SELECT
    s.label AS 'Survey',
    su.name AS 'Subject',
    ou.name AS 'Questioner',
    q.text AS 'Question',
    o.`option` AS 'Answer'
FROM Survey s
JOIN User ou
    ON s.User_id = ou.id
JOIN Question q
    ON q.Survey_id = s.id
JOIN Answer a
    ON a.Question_id = q.id
JOIN User su
    ON a.User_id = su.id
JOIN AnswerOptions AS ao
    ON ao.Answer_id = a.id
JOIN `Option` AS o
    ON ao.Option_id = o.id
WHERE
    ou.id = 1 AND 
    su.id = 2


What makes this query run at an acceptable level is the fact that all the fields used in the JOIN ON and WHERE conditions are indexed, and can therefore be queried extremely fast.
Was This Post Helpful? 1
  • +
  • -

#5 sas1ni69  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 85
  • View blog
  • Posts: 431
  • Joined: 04-December 08

Re: Dynamic Database Content Design

Posted 22 December 2012 - 08:46 PM

Hello,

Once again I really thank you for your comprehensive and truly thoughtful responses. I will be using the Yii framework for development and it encourages a lot of indexing to speed up development. So I guess that's good news for my soon-to-be db structure :)

Thanks again!

Hello,

Once again I really thank you for your comprehensive and truly thoughtful responses. I will be using the Yii framework for development and it encourages a lot of indexing to speed up development. So I guess that's good news for my soon-to-be db structure :)

Thanks again!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1