Can someone give a lot for many MySQL step-by-step guide?

Question

after searching up and down, reading all the possible articles and tutorials, I understood the basics of the concept, but still I can’t do this, and like so many others, as I see it.

Someone can send a 100% practical and fictitious proof of the manual on creating and most basic use of MySQL for many relationships, I am sure that many of them will benefit from it.

I have a table in which there are several elements, for example I1, I2, I3 ...

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

For each of the elements, each of the properties can be false or true, for example

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

So how do you go about building relationships? (please provide a code if possible)

And once created, like

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

Thanks in advance

Answer 1

Step 1 - installation tables:

You should have a table structure as shown below (* are primary keys):

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


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


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

Step 2 . Establish a foreign key relationship:

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

Step 3 - Code:

To associate properties with PropertyIds 35 and 44 with an element with ID 111, you will run the following:

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

, , :

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

Answer 2

, . , (), . ?

Answer 3

what I would do is create a table with a name ItemPropertieswith columns ItemId, PropertyIdand PropertyValue.

the key (ItemId, PropertyId)will be unique to this table.

Answer 4

This associates products with properties, allowing you to use 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;