Re: database design in a survey/poll system

On Nov 17, 2010, at 7:42 AM, 肖晗 wrote:

I plan to design a small survey/poll system similar to polldaddy<http://polldaddy.com/> . And I have some confusion in designing the database for the multiple/single choice questions. Of course, it is possible to use one table to store the question title and another table to store the choice item(one record for each choice item).

As you have a many-to-one relationship of answers to questions, two tables would be necessary in a normalized database.

My main concern is that whether we can place the choices together in the same table(and in one ) with the question title. I guess it can be faster to read from one table than reading from two table.

It is possible, but really, why bother? It's not a very time consuming function whether you join two tables or read from one and end up parsing the results. String parsing can be expensive, too.

And my idea is to use a delimiter to separate the choices. And the handling of the choices are done in the php script. But what delimiter should be used?

You can easily choose any character and just make sure your responses never include that character, or escape it somehow (eg. via \ )

Can anyone help? Thanks!

I really don't think you gain much, if anything, by having a single table in this instance. The retrieval is trivial for the sql engine, vs creating parsing code in PHP which may be trouble-prone or convoluted.