MYSQL storing input values in one row or multiple rows

Question

I'm working with PHP and MySQL, and I'm not too experienced with database design. I know the basics of normalization, however my question is about storing vales from a specific form I have.

Ok, so the way my page in question is set up, there are text boxes that can be dynamically added or removed. If the user presses a "+" or "-" button it adds or removes another text box.

Anyway, when I pull the text box values via $_POST it is an array, and then I convert it to a string using implode() Ex: user enters "hello" "world" "bye" "world", implode converts it to the following string "hello,world,bye,world".

My question is would it be better to store this string "hello,world,bye,world" in 1 row as 1 entry, or would it be better to break this string into 4 separate strings and store it in 4 different rows? Each text box will contain questions/comments so they could be potentially long and most likely not 1 word like the example I gave.

The reason why I'm asking this is because all of these inputs will be used on the same page, so they can all share a unique ID. Instead of querying multiple times, I'd only have to query once.

What happens if someone enters a comma in one of the fields? — Sam Dufel
– Sam Dufel, Commented Feb 17, 2012 at 1:42
Hmm, very true. I could always use a "~" or some rarely used character (and restrict the usage of it) but I'll just stick with the separate row method. — user1104854
– user1104854, Commented Feb 17, 2012 at 1:45

Joe · Accepted Answer · 2012-02-17 01:46:31Z

1

They should be stored separately, because they're separate examples of one kind of data.

Your table would be (for example) comments, and each row should be one comment, so one row for hello, one for world, etc.

You could also store a page ID alongside the comments, so your table would look like this (note that you'd typically have an id field for comment id, as well. I've omitted it for brevity):

|--------------|---------|
| comment      | page_id |
|--------------|---------|
| hello        | 1       |
| world        | 1       |
| bye          | 2       |
|--------------|---------|

Then you can still search on page_id, but your data is logically separated, and you can search on the specific comments if you wish.

SELECT * FROM comments WHERE page_id = 1 -- 2 results

You can also do things like ordering it alphabetically, or by time (with a new column for that) very easily.

|--------------|---------|------------|
| comment      | page_id | time_made  |
|--------------|---------|------------|
| hello        | 1       | 1234567890 |
| world        | 1       | 2356480546 |
| bye          | 2       | 5168161543 |
|--------------|---------|------------|

SELECT * FROM comments WHERE page_id = 1 ORDER BY time_made DESC -- most recent first

edited Feb 17, 2012 at 1:46

answered Feb 17, 2012 at 1:40

Joe

15.8k4 gold badges52 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1104854 Over a year ago

Thanks a lot for the examples. I'm going to store an ID beside them so I can reference it to that page like you suggested.

user319198 · Accepted Answer · 2012-02-17 01:51:30Z

1

it will be better to break this string into 4 separate strings and store it in 4 different rows in separate child table.

answered Feb 17, 2012 at 1:51

user319198

Collectives™ on Stack Overflow

MYSQL storing input values in one row or multiple rows

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related