469,920 Members | 2,488 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,920 developers. It's quick & easy.

Help with MySQL Many to Many Relationship

2
Hello,

I'm new to database development and need your help.

Although, I'm new I know that storing multiple entries into a database field is a horrible idea. Therefore, I'm trying to implement cross-reference/look-up tables.

Here's the problem I'm trying to solve...

I'm working on a restaurant guide site with restaurants having multiple dishes and dishes possibly being assigned to multiple restaurants.

Here is the structure of the two database tables:

restaurant
id
categoryid
name
price

dish
id
name

I setup the tables using the following codes:

CREATE TABLE restaurant (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
categoryid INT,
name VARCHAR(255) NOT NULL,
price VARCHAR(5) NOT NULL,
FOREIGN KEY (categoryid) REFERENCES category(id) ON DELETE SET NULL
) TYPE=INNODB;

CREATE TABLE category (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL
) TYPE=INNODB;

CREATE TABLE dish (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(25) NOT NULL
) TYPE=INNODB;

Here's how I setup the xref table:

CREATE TABLE restdish (
restaurantid INT NOT NULL,
dishid INT NOT NULL,
PRIMARY KEY(restaurantid, dishid)
) TYPE=INNODB;

With this all said and done I have a few questions:
  1. Will I need to go into the xref table and add in all the possible restaurant to dish id combinations?
  2. Am I missing a relationship between the restaurant and dish tables?

I'm thinking I need to place a restaurantid foreign key in the dish table to assign each dish combination to restaurants but then dishes will need to be repeated over and over again. I'm just thinking there's a better way to set things up.

Your help will be sorely appreciated!

OD Ntuk
Jan 22 '07 #1
5 3571
ronverdonk
4,258 Expert 4TB
Please read the Posting Guidelines before you post in this forum!.

This is a MySQL related question, and has nothing to do with PHP, so the PHP thread has been removed.

moderator
Jan 22 '07 #2
odntuk
2
Please read the Posting Guidelines before you post in this forum!.

This is a MySQL related question, and has nothing to do with PHP, so the PHP thread has been removed.

moderator
I apologize. I posted in the PHP section before I noticed that there was a MySQL section.
Jan 22 '07 #3
gyung
21
I suggest physically drawing out tables and draw lines to represent relationships. I usually do this when making any more than 3~4 tables just to make sure I'm not missing anything. When you have 10+ tables it really helps :D
Jan 22 '07 #4
ronverdonk
4,258 Expert 4TB
I suggest physically drawing out tables and draw lines to represent relationships. I usually do this when making any more than 3~4 tables just to make sure I'm not missing anything. When you have 10+ tables it really helps :D
You are absolutely right. In my opinion, drawing a database diagram must always be part of the normal design procedure. Without it you, or anyone after you who does maintenance on the code, are stuck when, say after 6 months, you have to re-design one or more tables and you don't have a clue as to how you did it in the past.

Ronald :cool:
Jan 26 '07 #5
cassbiz
202 100+
You are absolutely right. In my opinion, drawing a database diagram must always be part of the normal design procedure. Without it you, or anyone after you who does maintenance on the code, are stuck when, say after 6 months, you have to re-design one or more tables and you don't have a clue as to how you did it in the past.

Ronald :cool:
I recently had to redraw a complete table structure because I "winged" the first one and forgot some necessary fields.

Piece of paper or white board works real well.

Good Luck
Jan 27 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Gordon | last post: by
reply views Thread by | last post: by
reply views Thread by Tarbuza Smith | last post: by
reply views Thread by Vaclav Frolik | last post: by
4 posts views Thread by Simon Bond | last post: by
20 posts views Thread by Jack Schitt | last post: by
6 posts views Thread by Ridge Burner | last post: by
118 posts views Thread by Chuck Cheeze | last post: by
reply views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.