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;