many to many relationship in mysql have to be the foreign keys the primary keys from both tables con-CodePudding

Lets says that I have an order table and item table :

CREATE TABLE if not exists ORDERS (
    ORDERID INTEGER AUTO_INCREMENT,
    ORDERTYPE VARCHAR (20) NOT NULL,
    ShippedTime VARCHAR(40),
    ORDERDATE DATE,
    PRIMARY KEY (ORDERID),
);
CREATE TABLE if not exists ITEM(
    ITEMID INTEGER AUTO_INCREMENT,
    NAME VARCHAR (20) NOT NULL,
    PRICE INTEGER NOT NULL CHECK (PRICE > 0),
    PRIMARY KEY (ITEMID)
);

and the relation between the both tables will be existof :

CREATE TABLE if not exists EXISTOF (
  ORDERID INTEGER NOT NULL,
  ITEMID INTEGER NOT NULL,
  FOREIGN KEY (ORDERID) REFERENCES ORDERS(ORDERID)  ON DELETE CASCADE,
  FOREIGN KEY (ITEMID) REFERENCES ITEM(ITEMID)  ON DELETE CASCADE,
  PRIMARY KEY (ORDERID,ITEMID)
);

The explanation should be for each order has multiple item and each item belongs to many orders. If I do like this it will not be work because the ids are primary keys and I can't insert for specific order multiple item and also it can not items belongs to multiple order. Does anyone have any recommendation how to do that?

CodePudding user response：

Create an intermediate table OrderItems with foreign keys item_id and order_id. There are other options but this is the easiest way I find to break down many-many relationships!

CodePudding user response：

Your Existof Table is not flexible enough. The way most order processing systems deal with this situation is to add a column, which we can call Quantity, to the Existof table. The default value is 1, but other quantities can be put in as well.

So if a given order wants to order say 5 reams of paper,and ream of paper in a product, the entry for this item in Existof will have a quantity of 5.

This assumes that all 5 reams are interchangeable, and therefore described by the same data. If some of the paper reams are of different colors, than they ought to be different products.