how can we add two field in same column in sql table?

Question

i want to create a database that consists all products rating and notes purchase by user

suppose that i create a table name "Users_rating_product" that consists some column

user_id ->INT
company->Varchar
product1 -> 3 (rating of product out of 5)|(review of product)
product2-> 4(rating)|(some review)

i want to know how can i do it in mysql . i'm using phpmyadmin for database creation

table looks like

user_id  | company  |Ac                 | TV           | fridge 

1        | goderaj  |3 ,take more power |4,less power  |5,efficient

i want to know how can i do this so that both rating and notes for product display in same column

Savv · Answer 1 · 2014-10-31T10:25:32.643

0

For this you'll need a separate table. Call it ProductRating. Put a unique key (ID) to the table and reference that key to your Users_rating_product table.

For example:

Users_rating_product:

user_id  | company  |Ac  | TV | fridge 
1        | goderaj  |1   | 2  | 3
2        | somecomp |4   | 5  | 6

ProductRating

ID | Rating | Review
1  | 3      | Take more power
2  | 4      | less power
3  | 5      | efficient
4  | 5      | excellent
5  | 1      | awful
6  | 3      | average

edited Oct 31 '14 at 10:25

answered Oct 31 '14 at 10:13

Savv

433
2
7

but there are many users and for any user there are many company then how can be relate which product have which review? – SAC Oct 31 '14 at 10:19
Your table need a `user_id`. – Gordon Linoff Oct 31 '14 at 10:22
@SAC - I've added another user/company in my example to show you how it works. – Savv Oct 31 '14 at 10:27

score 0 · Answer 2 · edited May 23 '17 at 12:05

There are many ways that you can achieve your functionality. but this is not standard format to write queries.

you can have comma seperated column in your database like this
    user_id  | company  |Ac_mapping_id  
    1        | goderaj  |1,REview for Ac

for Seperate two values you can do like this

PARSENAME(REPLACE(String,',','.'),2) 'Name'

for Detail Reference for splitting two values you can refer : click To refer

Better Way is to store Product description in different table. You can easily write queries to retrieve your data

user_id  | company  |Ac_mapping_id  | TV_mapping_id | fridge_mapping_id 
1        | goderaj  |1              | 2             | 3

& store all the ratings & reviews in mapping table like this

Ac_mapping_id  | Rating   |Review
1              | 1        |abcd

So on for the other table.

For Retrieving all the data just Use Left outer join

Select 
*
from
Users_rating_product mm
Left outer join  Ac_mapping_table ac on ac.ac_mapping_id = mm.ac_mapping_id
.....so on

@SAC This is one of the way of normalization of your database — Hardik Parmar, Oct 31 '14 at 10:40

how can we add two field in same column in sql table?

2 Answers2