Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Location
    san jose, CA
    Posts
    68

    determine relationship between two tables?

    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;
    Last edited by sjgrad03; 11-23-08 at 03:16. Reason: type in the title

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2003
    Location
    san jose, CA
    Posts
    68
    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;

  4. #4
    Join Date
    Aug 2003
    Location
    san jose, CA
    Posts
    68
    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;
    Last edited by sjgrad03; 11-25-08 at 05:11.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •