NYCPHP Meetup

[George Webb]

Dear J.,

	According to a somewhat recent version of the MySQL manual,
enums are an efficient way to store the data you described, since
each item takes exactly one of a relatively few discrete values.
Also, the length of the enummed values you chose (e.g. 'good' /
'very poor' / etc.) doesn't really affect the storage or accessing
issues.  (So you can choose long, verbose, human-readable names just
as efficiently as short terse names.)

	I couldn't really follow your description, but it sounds if
you have a lot of enum fields in a table, each with the same set
of possible values, you might also want to consider having two
separate tables -- one main table, and a table of, say, high-medium-low
responses.  The 2nd table, would of course be keyed back to a unique ID
in the first table, as well as have its own field name key (i.e. which
high-medium-low question).

	I don't think it will matter as much for performance whichever
way you go.  The first way is simpler, but a little harder to add
fields, since you may have to hard-code all the column names into your
input HTML form, your PHP script, your MySQL table, and maybe also your
reporting HTML interfaces.  With the second (two-table) approach, you can
keep the list of fields flexible -- i.e. you won't have to change your
database structure whenever you add/remove fields.

	To be even more flexible, you could forget about the enum fields
(and efficiency thereof) and just store all the responses as TEXT
columns.  This would really allow you to easily add/remove fields from
your input and reporting interfaces.  If you have plenty of disk space
and CPU power at all times, you might rather trade efficiency for
flexibility.

	I recently built a "LAMP" form handler for a guy who had an HTML
questionnaire of some sort.  My handler simply cycles through all $_POST
fields, and inserts each field as its own row in the database.  So I made
two tables as described above: each questionnaire POST inserts one (1) row
into the first table, gets the last_insert_id from that operation, and then
stores a row in the other table for *each* of the $_POST fields/values.
The second table inserts also get keyed with that last_insert_id, in order
to tie each questionnaire's data together. The tables look like this:

mysql> desc q1_id;
+--------+--------------------------------+------+-----+---------+----------------+
| Field  | Type                           | Null | Key | Default | Extra          |
+--------+--------------------------------+------+-----+---------+----------------+
| id     | int(11)                        |      | PRI | NULL    | auto_increment |
| status | enum('NEW','DOWNLOAD','PURGE') |      |     | NEW     |                |
| ts     | timestamp(14)                  | YES  |     | NULL    |                |
+--------+--------------------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> desc q1;
+-------+--------------------+------+-----+---------+-------+
| Field | Type               | Null | Key | Default | Extra |
+-------+--------------------+------+-----+---------+-------+
| id    | int(11)            |      | MUL | 0       |       |
| name  | varchar(16) binary |      | MUL |         |       |
| value | text               | YES  |     | NULL    |       |
+-------+--------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)



	Then, the reporting interface works roughly like this:
1/ SELECT DISTINCT all field names from the second table -- in order to 
get a complete list of fields.  2/ SELECT second table LEFT JOIN first table
ORDER BY the common ID -- this groups all of the data for each submission
together.  3/ Loop through the results, watching for the common ID to change.
When it does, print out all the fields, in the order gotten in step 1/.
The result is a spreadsheet which changes dynamically whenever new fields
are added to the questionnaire.  I will never have to update the PHP
application or the MySQL structure.  The guy can change his own HTML fields,
and they will automatically appear in his spreadsheet report.

	I hope you find this half as interesting as I do!  Have fun re-writing!

Best,

George Webb
gw.nyphp at gwprogramming.com