determin relationship between two tables?

sjgrad03 · #1 (**permalink**) 11-23-08, 01:36

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

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 a 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 statement

Code:

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;

r937 · #2 (**permalink**) 11-23-08, 03:11

the only reason you'd want a 3rd table is if you want to allow for the possibility of have two users administer the same restaurant

alternatively, you mentioned restaurants "picked" by the user -- this could be a completely different relationship

so each restaurant has one admin user, but perhaps several users who eat there?

in that case, you'd have two different relationships, and yes, for a many-to-many relationship you'd need a 3rd table

by the way, in the restaurants table, the REFERENCES clause needs to reference User(adminID), not adminUser(uid)

sjgrad03 · #3 (**permalink**) 11-23-08, 13:47

Quote:

Originally Posted by r937

the only reason you'd want a 3rd table is if you want to allow for the possibility of have two users administer the same restaurant

alternatively, you mentioned restaurants "picked" by the user -- this could be a completely different relationship

so each restaurant has one admin user, but perhaps several users who eat there?

in that case, you'd have two different relationships, and yes, for a many-to-many relationship you'd need a 3rd table

Hello r937:
Thanks for the reply. i changed the name of the primary key in the Users table. I think your suggestion is correct, I need a third table to hold many-to-many relationship. I don't need admin user to manage the same restaurant.

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 will post my third table here for further suggestion.

My new table structure:

Code:

  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


create table restaurants(
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 Users (user_id)
)ENGINE=INNODB;

sjgrad03 · #4 (**permalink**) 11-25-08, 03:28

hello everyone:

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.

Code:

  #table Users
create table Users(
username varchar(255) not null,
user_id int(11) not null auto_increment,

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,
index (uid),
...
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.

Code:

     #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,
....
email varchar(40),
primary key (user_id)
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),
....
category varchar(50),
primary key (res_id),

)ENGINE=INNODB;

r937 · #5 (**permalink**) 11-25-08, 06:11

you are confused whether it should be many-to-many or one-to-many?

and you want our suggestion?

okay, make it many-to-many

the user_restaurants table should not be using an auto_increment, and it should have a primary key --

CREATE TABLE user_restaurants
( userID INTEGER NOT NULL
, resID INTEGER NOT NULL
, PRIMARY KEY (userID,resID)
, FOREIGN KEY (userID) REFERENCES Users (user_id)
, FOREIGN KEY (resID) REFERENCES restaurants (res_id)
);

you might also want to use the same column name in both places for consistency, i.e. don't use user_id in one place and userID in the other

Internet Services

Web Development

Digital Marketing

Consumer Tech