determin relationship between two tables?

[sjcoder07]

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

[George1111]

This is not very clear

You have a Restaurant table which must have

1) Restaurant ID
2) Restaurant Name
3) Category of Restaurant
... all other details about the restaurant


The other Table (User) is a person who subscribes to the restaurant enquiry so would look something like

1) UserID
2) User Name
.... any other details about the user (email, address, phone, etc)


When a User Picks a restaurant, you need to make another Table - lets call it UserFavourites

Layout would be

1) UserID
2) RestaurantID

With a recorded written for each Restaurant selected

So, to select all the restaurants that a user has as favourites you would say

Numeric UserID

UserIDSelected = 123
StrSql = "Select * from UserFavourites where UserID = " & UserIdSelected


Alpha UserID

UserIDSelected = "ABC"
StrSql = "Select * from UserFavourites where UserID = '" & UserIdSelected & "'"


I think thats what you are trying to achieve but I'm not completely sure ?

[sjcoder07]

This is not very clear
I think thats what you are trying to achieve but I'm not completely sure ?

Hello George:
Thanks for the reply! The idea is based on the users' input (restaurant name, category-Italian, Chinese, American, some other criterias ) show them a list of restaurants. If a restaurant get most vote, then users will go there for lunch.

i am confused about relationships between the tables. Could you or anyone from the forum help me to clarify which relationship shall I use.

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,
...
primary key (user_id)
)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,
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 resID 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,
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,
...
primary key (res_id),

)ENGINE=INNODB;

[George1111]

So you need 3 Tables

Users
Restaurants
RestaurantsSelected (by Users)

Users Table
UserID
UserName
UserDetails etc

Restaurants table
RestaurantID
RestaurantName
RestaurantType
RestaurantStarRating
RestaurantDetails etc

RestaurantsSelected
RestaurantID
UserID
DateSelected
RatingGiven
UserComments etc

Thats it

A user logs in (Opens User Table)

A user selects a restaurant they like from a list (or even add a new restaurant to the Restaurant Table)

When they click "VOTE" button, you write out a record to the RestaurantsSelected Table along with user comments and Rating Given

From time to time you run a process which adds up all the votes per restaurant and turns it into a STAR rating in the Restaurants File