I've got a product table with 100000 items as well as I've got a user table with 5000 records. and suppose a person can provide us feedback about any mixture of this items. So suppose he selects to check items 1,3,100,200,400,500 and so forth. (he is able to send us another feedback about another items)
Here is my question, Among the finest to be aware what a specialist database designer would consider this case where the amount of items and customers are large. One method to store this rankings are in one string like: 1#5 3#4 100#5 .... x#y implies that he gave y stars to product with id of x. I'm able to load this election_string during my php script for instance and extract the particulars from it.
Therefore the rating table may likely have this structure: id, user_id, election_string
One other way would be to store this feedback within this structure:
election Table: id, user_id, date
election_particulars Table: election_id, product_id, election ==> (election_id,items_id) as primary key and election_id is foreign key.
(or may these 2 tables could be compressed in one table like id,user_id,product_id,election [id,user_id,product_id] as primary key)
And so i guess it is extremely simpler to question within the second design however it consumes more room and time for every query, also just in case of product deletion the correct answer is easier to make use of the second design. How would you react?
Any type of idea is appriciated.
you rarely wish to go in the future of concatenating strings in database land. Just makes existence really painful for doing queries - as well as, not necessarily the way in which DB's are made to process data.
think that you would like your next approach. You have to consider primary secrets and so on election for any product two times?
By storing it as being a string, like 1#5 3#4 100#5, you're which makes it harder later to produce reviews. You might also need to complete some string manipulation each time you should utilize the information. For any simple structure such as this I do not begin to see the benefit.
I'd choose one table (id, userid, productid, election), but I am sure two is okay too.
By concatenating you've just wiped out any ability the db has of forcing integrity or utilizing an index to effectively search if your user has chosen for the product.