CODEWITHSUNDEEP
databasedatabase-design
puser
puser

Reputation: 477

Can/how would you make a database where two items have multiple relations between them

For this, I have sandwiches and chefs.

Sandwich has one key, its primary key, - Name.

Chef has its primary key - Cid, other information keys, such as name, age, etc.

The relationships I need are

SignatureSandwich, with which, each chef has one sandwich which is their main creation. one-to-one

LikesSandwich, with which each chef can like to eat any number of sandwiches (which may not include their own signature sandwich) and any sandwich can be liked by any number of chefs. many-to-many

Would this just be implemented with two relationships, each having the foreign keys Cid and Sandwich.Name, or is there a better way of doing it?

Upvotes: 2

Views: 96

Answers (3)

Dan Bechard
Dan Bechard

Reputation: 5291

I see SignatureSandwich being a property of Chef, i.e., a column in tblChef. SignatureSandwich should have a foreign key relationship to tblSandwich.Name.

The LikesSandwich relationship I would model with a new table, tblSandwichPreference, which has two columns: Cid and SandwichName.

In tblSandwichPreference, Cid should have a foreign key to tblChef.Cid and SandwichName should have a foreign key to tblSandwich.Name.

Some useful queries:

-- Query all chefs who like a particular sandwich
SELECT * FROM tblSandwichPreference
JOIN tblChef ON tblChef.Cid = tblSandwichPreference.Cid
WHERE tblSandwichPreference.SandwichName = @SandwichName

-- Query all sandwiches preferred by a particular chef
SELECT * FROM tblSandwichPreference
JOIN tblSandwich ON tblSandwich.Name = tblSandwichPreference.SandwichName
WHERE tblSandwichPreference.Cid = @ChefID

Upvotes: 4

zero298
zero298

Reputation: 26920

So something like this? This is mysql, if you aren't using it already, download the mysql workbench and tinker with it. It gives you a ton of insight on how SQL works. I use to code this stuff by hand, but visualizing it is really helpful.

enter image description here

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

DROP SCHEMA IF EXISTS `mydb` ;
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`Sandwiches`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Sandwiches` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Sandwiches` (
  `SandwichName` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`SandwichName`),
  UNIQUE INDEX `SandwichName_UNIQUE` (`SandwichName` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Chefs`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Chefs` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Chefs` (
  `ChefID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(45) NOT NULL,
  `Age` INT UNSIGNED NOT NULL,
  `SignatureSandwich` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`ChefID`, `SignatureSandwich`),
  INDEX `fk_Chefs_Sandwiches_idx` (`SignatureSandwich` ASC),
  CONSTRAINT `fk_SignatureSandwich`
    FOREIGN KEY (`SignatureSandwich`)
    REFERENCES `mydb`.`Sandwiches` (`SandwichName`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`SandwichLikes`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`SandwichLikes` ;

CREATE TABLE IF NOT EXISTS `mydb`.`SandwichLikes` (
  `SandwichThatIsLiked` VARCHAR(45) NOT NULL,
  `ChefThatLikes` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`SandwichThatIsLiked`, `ChefThatLikes`),
  INDEX `fk_Sandwiches_has_Chefs_Chefs1_idx` (`ChefThatLikes` ASC),
  INDEX `fk_Sandwiches_has_Chefs_Sandwiches1_idx` (`SandwichThatIsLiked` ASC),
  CONSTRAINT `fk_SandwichThatIsLiked`
    FOREIGN KEY (`SandwichThatIsLiked`)
    REFERENCES `mydb`.`Sandwiches` (`SandwichName`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ChefThatLikes`
    FOREIGN KEY (`ChefThatLikes`)
    REFERENCES `mydb`.`Chefs` (`ChefID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Upvotes: 2

esauro
esauro

Reputation: 1286

SignatureSandwich is trivial, just put a ForeignKey on Chef to sandwich.

For LikesSandwich you should use an intermediary table, because each chef can likes many sandwiches and a sandwich can be liked by many chef.

You can know the favourite sandwiches of a Chef filtering in tha intermediary table, and you can know who fauvorited a sandwich by filtering in that intermediary too.

Upvotes: 1

Related Questions