determine relationship between two tables?

[olive08]

Hello everyone:

I am designing a database for a web application. The idea is user should able to
pick a restaurant by enter restaurant name, category(American, Chinese) etc

I currently have two tables: user table and restaurant table.
I have adminID as a primary key in user table.
I used adminID as a foreign key in restaurant table.

I thought the relationship between these two tables is one to many. For each adminID in user table there are many corresponding records in restaurant table, but each restaurant can have only one adminID in the user table.

However, i feel that the restaurant table should only hold information about the restaurant. It is not holding restaurant(s) picked by the user, I feel like there should be 3rd table to hold uid and res_id that connect user table and restaurant table.

Please help me to clarify the relationship between these two tables. Should I add 3rd table in this case? I appreciate your help!

create table command:

create table User(
username varchar(255) not null,
adminID 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 restaurant
create table restaurant(
res_id int(11) not null,
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),
foreign key (uid) references adminUser (uid)
)ENGINE=INNODB;

[timothybard]

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...

[olive08]

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;

[timothybard]

With the rating entity I mentioned, there is a one-to-many relationship from rating to user and a one-to-many rating from rating to restaurant. This will allow each user to rate many restaurants and each restaurant to be rated by many users.

There is a many-to-many relationship from users to restaurants with ratings being the cross reference table between the two.