Yes, a third table would be appropriate in this situation. I would call the table something like "rating" and have attributes like this: ID(PK), UserID(FK), RestaurantID(FK), Rating, DateRated, etc...
Hello timothybard:
Thanks for the reply.
I am confused about what relationship shall I use.
please give me some suggestions. ThankdS
1. for a many-to-many relationship, i created a 3rd table: user_restaurants
user_restaurants table serve as a link between Users and restaurants.
a user can pick several restaurants, one restaurant can be selected by several users.
#table Users
create table Users(
username varchar(255) not null,
user_id int(11) not null auto_increment,
password varchar(255) not null,
fname varchar(31),
lname varchar(31),
email varchar(40),
primary key (adminID)
)ENGINE=INNODB;
#table restaurants
#
#should I use uid as a foreign key
create table restaurants(
res_id int(11) not null auto_increment,
uid int(11) not null,
index (uid),
name varchar(40),
address varchar(80),
city varchar(30),
state char(2),
zip varchar(15),
menu varchar(50),
category varchar(50),
primary key (res_id),
)ENGINE=INNODB;
#
#
create table user_restaurants(
userID int(11) not null,
resID int(11) not null auto_increment,
foreign key (userID) references Users (user_id),
foreign key (resID) references restaurants (res_id),
)
2. for a many-to-one relationship.
2a) I created res_id as a foreign key refers back to restaurants.
in many to one relation, one user can only select one restaurant
but one restaurant can be selected by multiple users.
#table Users
create table Users(
username varchar(255) not null,
user_id int(11) not null auto_increment,
resID int(11) not null auto_increment,
password varchar(255) not null,
fname varchar(31),
lname varchar(31),
email varchar(40),
primary key (adminID)
foreign key (resID) references restaurants (res_id)
)ENGINE=INNODB;
#table restaurants
create table restaurants(
res_id int(11) not null auto_increment,
uid int(11) not null,
index (uid),
name varchar(40),
address varchar(80),
city varchar(30),
state char(2),
zip varchar(15),
menu varchar(50),
category varchar(50),
primary key (res_id),
)ENGINE=INNODB;
Unsolved 
