to save as text block? or to save as rows?

[ryan morris]

Hello --

I am faced with a decision that I have been weighing, and would love to hear some feedback.

I have developed a system where a user is able to submit entries to the database on any subject in any category. These 'entries' are one row in an 'entries' table. Every category is able to have any number/type of form field that the user must submit -- so one category may ask for 3 text fields, while another may ask for 7 text fields and a file field.

I am trying to decide whether it makes more sense to just have all the 'variable' form field submissions be saved in one Text column in the entries table (so that every entry has one row, and all the 'variable form fields' are stored in one text column, delimited by a special string.

..or would it be better to have a new table, say "entry_fields", which submits a row for every custom field that is submitted. So, if a user submitted to a category that asked for 3 form fields, 3 rows would be created, one for each.

Though I know the 'row per custom field entered' may be more normalized, the system will be heavily searched & browsed, so I am leaning towards the 'text block' solution to avoid extra queries... I also expect to have a large amount of entries -- and if each entry has on average 5 'custom fields', then that is 5x the amount of entries needed for the 'entry_fields' table.

As a moderate user (not a pro) of mysql, I am wondering which is the best way to go for speed & order. Any help at all is appreciated!

Thanks,
RM



Edited 1 time(s). Last edit at 11/10/2006 06:10PM by ryan morris.