Storing an array in a string (Database experts question)

Question

I have a product table with 100000 products and also I have a user table with 5000 records. and suppose a user can give us feedback about any combination of this products. So suppose he chooses to compare products 1,3,100,200,400,500 and so on. (he can send us another feedback about some other products)

So here is my question, I just want to know what an expert database designer would think about this situation where the number of products and users are big. One way to store this ratings are in a single string like: 1#5 3#4 100#5 .... x#y means that he gave y stars to product with id of x. I can load this vote_string in my php script for example and extract the details of it.

So the rating table would likely have this structure: id, user_id, vote_string

Another way is to store this feedback in this structure:

vote Table: id, user_id, date

vote_details Table: vote_id, product_id, vote ==> (vote_id,products_id) as primary key and vote_id is foreign key.

(or may these 2 tables can be compressed in a single table like id,user_id,product_id,vote [id,user_id,product_id] as primary key)

So i guess it's very easier to query in the second design but it consumes more space and time for each query, also in case of product deletion it's quite better to use the second design. What would you do?

Any kind of idea is appriciated.

Answer 1

you almost never want to go down the road of concatenating strings in database land. Just makes life really painful for doing queries - and also, not really the way that DB's are designed to process data.

think that you want your second approach. You need to think about primary keys and the like {i.e can the same person vote for a product twice?}

Answer 2

By storing it as a string, like 1#5 3#4 100#5, you are making it harder later to create reports. You also have to do some string manipulation every time you need to use the data. For a simple structure like this I don't see the benefit.

I would go for one table (id, userid, productid, vote), but I'm sure two is fine as well.

Answer 3

Ehsan

By concatenating you have just killed any ability the db has of forcing integrity or using an index to efficiently search if a user has voted for a particular product.