How to organize the connection of the "parent" table with 2 identical data sets in the "child"?

Question

Computers

Hello.
There is table A, two records from table B refer to it
.
Those. if, according to the application logic, object A consists of 2 objects B
AND there is also a search, difficulties when it is necessary to search for two objects in one request, decided under the request for the first object and add. selection on it by search on compliance to conditions to the second object.
The fact is that mysql does not know that they are logically divided into two DIFFERENT objects for it, these are just two identical objects with different data.
Because the data is completely identical, it was placed in one table in different fields, each of them is still referenced by an additional table with data.
Perhaps it is necessary to organize storage somehow differently to facilitate the search algorithm?

Answer 1

In general - quite good. But if there is an opportunity, then it is better to raise the RAM to 16 GB.
IMHO, but it's better to wait a week: wait for some tests, compare the price-performance ratio between the RX 480 and GTX 970, and then decide.

Answer 2

Personal opinion:
1. Or Intel + Geforce, or AMD + Radeon. Mix them - the collective farm is full. I chose the first option for myself.
2. The manufacturer of the video card is better to take the same as the motherboard (GIGABYTE).
3. For KINGSTON RAM 16 gigabytes is good.
4. The hard drive would take WD Green at least 3 TB.
5. The motherboard would be taken by GIGABYTE on the H170 chipset.
6. The power supply would take Corsair.
7. The monitor would take ASUS.
8. The case would take Fractal Design DEFINE R4 (or R5).

Answer 3

Make an intermediate table with the keys id_a, id_b1, id_b2. You will have an entity that describes a complex relationship between A and B, just consider the symmetry id_b1 id_b2

Answer 4

Your structure is normal, given that table B has an opting type column.
There is another option is to make 2 different tables, since for different entities it is nonsense to make one table, although their data types are the same.

Answer 5

The word "connection" is not very clear. You mean concept "relation" of a relational data model? ( wikipedia )

CREATE TABLE `A` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`ID`)
);

CREATE TABLE `B` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT,
  `A_ID` INT(11) NOT NULL,
  PRIMARY KEY (`ID`)
);

-- связь между дочерними записями и родительской записью
ALTER TABLE `B` ADD CONSTRAINT `B_A_ID` FOREIGN KEY `B` (`A_ID`) REFERENCES `A` (`ID`);

And the fact that you should have no more than two B records referring to A.ID is not solved by MySQL tools. It would be possible to solve triggers, but it is impossible to address records of the same table in the trigger.
Therefore, it is decided by external means, in the application.

Answer 6

1. you can use join
2. Why do you need 2 identical tables - the error is right here. Transfer everything to 1 and add a field in it that will distinguish the data

Answer 7

I give a sql dump to show by example, I propose to continue the discussion under this comment so as not to jump on others.
/*
MySQL Backup
Source Server Version: 5.5.41
Source Database: testtt
Date: 09/01/2016 23:02:55
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `A`
-- ---------- ------------------
DROP TABLE IF EXISTS `A`;
CREATE TABLE `A` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`field1` int(11) DEFAULT NULL,
`field2` int(11) DEFAULT NULL,
`field3` int(11) DEFAULT NULL,
PRIMARY KEY ( `id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for `B`
-- ---------- ------------------
DROP TABLE IF EXISTS `B`;
CREATE TABLE `B` (
`id` int(11) NOT NULL,
`a_id` int(11) NOT NULL,
`price` int(11) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
PRIMARY KEY (` id`),
KEY `fk_a` (`a_id`),
CONSTRAINT `fk_a` FOREIGN KEY (`a_id`) REFERENCES `A` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------------
-- Records
-- ----------------------- -------------
INSERT INTO `A` VALUES ('1','1','2','3'), ('2','1','2','3'), ('3','1' ,'2','3'), ('4','1','2','3');
INSERT INTO `B` VALUES ('0','1','25','1'), ('1','1','20','0'), ('2','2' ,'45','1'), ('3','2','55','0'), ('4','3','25','1'), ('5' ,'3','59','0');
I'll throw off at the same time the screenshots of the initial data
Table A
Table B
Task: By the most optimal means, get object A, which corresponds to the following condition, object A has a child element from B with type 0 price=20, and with type 1 price=25;
Query OR
SELECT `A`.* FROM A LEFT JOIN B ON `A`.`id` = `B`.`a_id` WHERE (B.price=25 AND type=1) OR (B.price=20 AND type =0);
Result:
As you can see, we get 3 objects, the first repeating ones, and if they are grouped, they correspond to the condition, but the third one, which did not suit us, got into the sample. comparison condition OR
Query AND
SELECT `A`.* FROM A LEFT JOIN B ON `A`.`id` = `B`.`a_id` WHERE (B.price=25 AND type=1) AND (B.price= 20 AND type=0);
It will not give a result, because contradiction in condition
UNION query is similar to OR as far as I remember, I won't give it now
My solution:
Through subquery
SELECT `subquery`.* FROM (SELECT `A`.* FROM A LEFT JOIN B ON `A`.`id` = ` B`.`a_id` WHERE (B.price=25 AND type=1)) subquery LEFT JOIN B ON `subquery`.`id` = `B`.`a_id` WHERE (B.price=20 AND type=0 );
We get the desired result
Because the data is very voluminous and in large quantities, but because at the moment, about a million of them work quite quickly with a subquery of the order of 1.8s, but the question of the correct solution is now for the future, how difficult the subquery is, given that in real data there are more than 3 left joins, including through has_many
. it may be all the same to spread the child elements of table B to different tables in order to get rid of the subquery.