Efficient MySQL Database Structure for Dynamic Form Creation

Question

I'm creating an application in Codeigniter which will allow anyone, without signing in, to create a form to be filled out using different input types (using text boxes, dropdowns, checkboxes, etc.). This form could be 1-100 questions and when completed it will be emailed to someone else who will then fill it out on the site.

I first set up my MySQL database similar to this post, with quite a few different tables all with only a few columns. I then indexed and used foreign keys to link the information.

Since then, I have changed and set up my database like this so I'm making fewer queries:

Document
  id, name, email, recipientname, recipientemail, document name
Document Questions
  document_id, question_id, question, type, comments

Is having more tables with fewer columns but more queries more efficient than how I'm doing it now? I understand that normalization plays a role, but to what extent are you hindering performance by making your tables so specifically small?

JamesHalsall · Accepted Answer · 2011-02-09 13:24:17Z

From a normalization point of view there are things you could do to further normalize your data (recipients could have their own entity and types could also), but it's not always the most optimal way of accessing your data.

For example, if you split your problem into 4 different entities (Types could just as easily be an ENUM):

Documents
Document Questions
Recipients
Types

Then to fetch a single form for your application you would be executing a query with multiple joins. If you're using MyISAM then all four of your tables become locked until the query finishes. Queries with bad joins and bad indexes can become very slow.

A better alternative would be to execute four separate queries on the database (add indexes relative to the most common queries you're running) to retrieve your data, this way tables will stay locked for a shorter period of time.

I know this is an extreme example, but I would concentrate more on your index optimization and strike a good balance between normalization and performance.

To sum up, sometimes fully normalized data means lower performance.

Collectives™ on Stack Overflow

Efficient MySQL Database Structure for Dynamic Form Creation

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related