Move table datas to another table field

Question

Have a task to make votes for photo. Because users can vote only one time for photo, I should save them. I plan do not to make separate table for saving user ids, but save them in photo table in blob field separating by any delimiter. What do you think about such practice? Are there any hidden troubles? I see with such structure, I will reduce count of queries and joins.

Answer 1

Don't do this. What you're trying is not uncommon, but it's nearly always ill-advised. Make another table to store the votes per photo per user. You're operating under the (false) assumption that reducing your joins and storing this data in a blob field will result in a performance benefit versus storing the votes in a table, and that's very (very) unlikely.

Some thoughts:

1. In doing this, you're going to be required to bring back every vote for a given photo if you want to examine any of the votes.
2. This is a recipe for corruption. Storing the user ID's in this field breaks the ability to maintain a foreign key on the votes. In other words, if a user record is modified or removed, there is no way to update the vote records.

Answer 2

I think the issue you may run into is one of scalability. If this photo voting site becomes quite popular, these blobs will get quite big, and will place more significant load on your server-side processing language (php, asp, etc..).

My general rule of thumb- databases are designed by programmers, much better than I, at doing one thing and doing one thing well- handling data. Your processing of the data most likely will not be as efficient as what the database can do. In otherwords- use a join, instead of post-processing it yourself.

Answer 3

A blob field with a delimiter is one of the absolute worst ways you could store the data. Store it correctly ina child table with a foreign key and an index on the foreign key.