CODEWITHSUNDEEP
mysqlsql
Raymond Gitonga
Raymond Gitonga

Reputation: 171

One to many relationship MYSQL

I'm new to mysql and am trying to link two tables and am not sure how, i have a products table with a list of products in a restaurant, the column in this table are

The other table is called extras, this table contains extra things that you can add to your order but are optional, for when buying chicken you have an option of spicy and non spicy, Some products have more than one extras, for instance a product can have the option of choosing three extras.

The extras table at the moment only has

not sure how to link the two or where to put foreign constraints.

UPDATE

Same extra may also belong to numerous products

Upvotes: 0

Views: 740

Answers (3)

Saksham Chaudhary
Saksham Chaudhary

Reputation: 637

In your case one product may have many extras(1 to many) and many products may be having same extra thing(many to 1). Thus, this is a many to many relationship and for such relations we need 3 tables.

CREATE TABLE products ( 
    id INT PRIMARY KEY AUTO_INCREMENT, 
    name VARCHAR(50), 
    price DECIMAL(10.2)
 );

 CREATE TABLE extras (
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(50)
 );

 CREATE TABLE products_extras (
      id INT PRIMARY KEY AUTO_INCREMENT,
      pro_id INT,
      FOREIGN  KEY(pro_id) REFERENCES products(id),
      ext_id INT,
      FOREIGN KEY(ext_id) REFERENCES extras(id)
 );

Upvotes: 1

Chris T-Pot
Chris T-Pot

Reputation: 567

A joining/linking table usually uses many-to-many relationships by joining the 2 parent tables/primary keys to allow many products to have many extras or no extras at all.

enter image description here

so for example:

eg: Product IDs (primary keys) are: 1, 2, 3 Product names are: chicken wings, chicken breast, chicken fillet

Extras IDs (primary keys) are: 1, 2, 3 Extras names are: mild, medium, hot

Wings, breasts and fillet have the option of all three of the extras, so the product_extras table would end up looking something like this:

product_extras_id | product_id | extras_id
------------------------------------------
1                 | 1          | 1
2                 | 1          | 2
3                 | 1          | 3
4                 | 2          | 1
5                 | 2          | 2
6                 | 2          | 3
7                 | 3          | 1
8                 | 3          | 2
9                 | 3          | 3

Upvotes: 1

JoshGivens
JoshGivens

Reputation: 148

With products having many extras and extras applying to many products this is actually a many to many relationship.

create table products(ID int auto_increment Primary Key,
Name varchar(50),
Price decimal(6,2));

create table extras(ID int auto_increment Primary Key,
Name varchar(50));

create table product_extras(Product int Not Null,
Extra int Not Null,
FOREIGN KEY(Product) References products(ID) ON DELETE CASCADE,
FOREIGN KEY(Extra) References extras(ID) ON DELETE CASCADE);

Something akin to this should work for you, though you may want to change the datatypes based off preference and what data actually needs to go in there.

Upvotes: 1

Related Questions