Can anyone give many to many MySQL step-by-step guide?

Question

Hi everyone, after searching up and down, reading all possible articles and tutorials, I grasped the basics of the concept, but still cannot do it, and so as many others as I can see.

Can someone please post 100% practical and dummy proof guide to creation and most basic usage of MySQL many to many relationship, I'm so sure many will benefit from it.

What I have is a table that has a number of items, say I1, I2, I3...

I have another table, with a number of properties, say P1, P2, P3...

For each of the items, each of the properties may hold false or true, for example

I1 has properties P2 and P3 I2 has properties P1, P2 and P3 I3 has properties P1 ...

So how to go about creating the relationship? (please give code if possible)

And once created, how to

• insert properties for some item I
• read which properties apply to some existing item I

Thanks in advance

Answer 1

Create an intermediary table between the two, that has ids from both the other tables. That way there can be multiple references (rows) in this table connecting different items to multiple different properties. Does this make sense?

Answer 2

what I would do is create a table called ItemProperties with columns ItemId, PropertyId and PropertyValue.

the key (ItemId, PropertyId) would unique for this table.

Answer 3

This links products to properties, allowing multiple items with the same name.

CREATE TABLE products (
  productid int(11) NOT NULL AUTO_INCREMENT,
  productname varchar(20) NOT NULL,
  description text NOT NULL,
  PRIMARY KEY (productid),
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE products_property (
  relationid int(11) NOT NULL AUTO_INCREMENT,
  productid int(11) NOT NULL,
  propertyname int(11) NOT NULL,
  PRIMARY KEY (relationid)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE properties (
  property varchar(30) NOT NULL,
  value enum('true','false') NOT NULL,
  PRIMARY KEY (property)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Answer 4

Step 1 - Setup tables:

You should have a table structure like below (*'s are primary keys):

Item
---------
ItemId*
ItemName


ItemProperties
--------------
ItemId
PropertyId


Properties
----------
PropertyId*
PropertyName

Step 2 - Set foreign key relationships:

Both columns in the ItemProperties table are foreign keys to their corresponding table (ItemId to Item table, PropertyId to Properties table)

Step 3 - Code:

To associate the properties with PropertyIds 35 and 44 to the Item with ID 111 you would run the following:

INSERT INTO ItemProperties (ItemId,PropertyId) VALUES (111,35)
INSERT INTO ItemProperties (ItemId,PropertyId) VALUES (111,44)

To select all properties associated with an item you would run the following:

SELECT ip.PropertyId, ip.PropertyName
FROM Item as i
INNER JOIN ItemProperties as ip ON i.ItemId = ip.ItemId
WHERE i.ItemId = 111