2
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:
- Will I need to go into the xref table and add in all the possible restaurant to dish id combinations?
- 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
