需要一些建议和反馈在MySQL中的许多许多关系编码 [英] Need some advice and feedback on coding a many:many relationship in MySQL
问题描述
我在编写许多关系时遇到一些困难，如下图所示：
我想要这些表和关系设置为：
- 父母可以有多个孩子（这里的孩子是
玩家），孩子可以有一个或多个父母最大
二）。 - 家庭可以由一个或多个家长（最多两个），
以及一个或多个儿童（无限制）组成。
我不知道如何容纳父母姓名不同于他们的孩子，反之亦然。我没有被告知这是否会是一个问题，但这是我认为值得考虑的事情。 Parent和Player类都继承人超类的字段（例如名字，姓氏，地址，年龄，dob等）。
我要测试一些输入家庭表，当我意识到，我不知道如何插入一个或多个父母与一个或多个孩子，他们是父母（或看护人）。我以为我有理由，但在测试我意识到，它不会工作，我花了90分钟以上的草图上的一些想法，但我只是迷路了。
========================================== ==================================
更新：27-04-2012 @ 22:19 NZST
我应该给出了结果我在寻找 - 当用这些表查询数据库时。这里是视觉表示：
+ ------------------- + ----------------- + --------------------- +
| ParentsFirstName | ParentsLastName |儿童家庭|
+ ------------------- + ----------------- + ------- -------------- +
| Gregory | Peck |迈克尔|
| Laura | Peck |迈克尔|
| Martha | Petersen |马特，克里斯托弗|
|克里斯| Michaels |理查德，肖恩|
| Nadine | Michaels |理查德，肖恩|
| Barry | Dackers |哈利|
|凯文| Mitchell |丹尼尔|
| Rebecca | Mitchell |丹尼尔|
+ ------------------- + ----------------- + ------- -------------- +
一个称为Player的表，而Parents在一个名为Parent的表中。在这篇文章中的MySQL代码表示关于这个特殊问题的表（你应该注意到我使用Person类作为超类，父/子表作为子类）。一些其他表通过使用外键引用（schoolID字段来自称为School的表，其具有schoolName字段）。
我不知道如果我已经构建我的表正确的我想要实现，但做一些研究我发现了一个函数）...
SELECT
ParentID，
Parent.FirstName ParentFirstName，
Parent.LastName ParentLastName，
PlayerID，
Player.FirstName PlayerFirstName，
Player.LastName PlayerLastName
FROM
父级
LEFT JOIN Player
ON Parent.ParentID = Player.MotherID
或Parent.ParentID = Player.FatherID
ORDER BY ParentId
...并且在应用程序代码中旋转数据
上述模型允许 Parent 的性别不匹配及其母/父角色。如果你想防止这种情况，你可以去做这样的事情...
...但我宁愿不复杂，坚持第一个模型，并强制执行此应用程序
I am having some difficulty in coding a many:many relationship, as shown in the image below:
I am wanting these tables and relationships set up so that:
- A parent can have more than one child (the child here is the "Player"), and a child can have one or more parents (a maximum of two).
- A "family" can consist of one or more parents (maximum of two), and also one or more children (unlimited).
I am not sure how to accommodate parents that have different surnames than their "children" and vice versa. I have not been informed if this will be a concern, but this is something I think is worth thinking about. Both Parent and Player classes inherit fields (like first and last names, addresses, age, d.o.b, etc) from a person superclass.
I was about to test some input into the family table when I realised I wasn't exactly sure how to insert one or more parents with one or more children that they are parents (or caregivers) of. I thought I had this sorted out in theory but upon testing I realised that it wouldn't work and I have spent over 90mins doing sketches on some ideas but I just got really lost.
==================================================================================
UPDATE: 27-04-2012 @ 22:19PM NZST
I should have gave a visual representation of the results I am looking for - when querying a database with these tables in it. Here is the visual representation:
+-------------------+-----------------+---------------------+
| ParentsFirstName | ParentsLastName | ChildrenInFamily |
+-------------------+-----------------+---------------------+
| Gregory | Peck | Michael |
| Laura | Peck | Michael |
| Martha | Petersen | Matt, Christopher |
| Chris | Michaels | Richard, Shaun |
| Nadine | Michaels | Richard, Shaun |
| Barry | Dackers | Harry |
| Kevin | Mitchell | Daniel |
| Rebecca | Mitchell | Daniel |
+-------------------+-----------------+---------------------+
The "children" are in a table called "Player", and the Parents are in a table called "Parent". The MySQL code in this post represents the tables concerning this particular problem (you should notice that I am using the Person class as a super class, and the parent/child tables as sub classes). Some other tables are referenced through the use of foreign keys (the "schoolID" field comes from a table called "School", which has a "schoolName" field).
I am not sure if I have constructed my tables right for what I am wanting to achieve, but doing some research I found a function called GROUP_CONCAT, which at least gave me a thought of what a query may look like - for this particular problem.
Accommodating parents that don't share the same last name, as well as children that don't share the same last name as the parents, is another big challenge I can't even try to wrap my head around (I'd imagine this would be the case for foster families as well). So for the above visualization I am assuming the non-single parents are married and share the same last name, and the children all share the same last name as both of the married parents.
==================================================================================
Here is some code that I have tried to go about creating the part of the database that tries to deal with this part (NOTE: the "Players" are the "children" of the parents):
DROP TABLE IF EXISTS `person` ;
CREATE TABLE `person` (
`personID` INT(5) NOT NULL AUTO_INCREMENT ,
`firstName` VARCHAR(50) NOT NULL ,
`lastName` VARCHAR(50) NOT NULL ,
`dateOfBirth` DATE NOT NULL ,
`personType` CHAR(6) NOT NULL,
`photo` BLOB NULL DEFAULT NULL ,
PRIMARY KEY (`personID`))
ENGINE = InnoDB;
SHOW WARNINGS;
DROP TABLE IF EXISTS `parent` ;
CREATE TABLE `parent` (
`parentID` INT(5) NOT NULL,
FOREIGN KEY (`parentID`) REFERENCES `person` (`personID`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
SHOW WARNINGS;
DROP TABLE IF EXISTS `player` ;
CREATE TABLE `player` (
`playerID` INT(5) NOT NULL,
`schoolID` INT(5) NOT NULL,
FOREIGN KEY (`playerID`)
REFERENCES `person` (`personID`)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (`schoolID`)
REFERENCES `school` (`schoolID`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
SHOW WARNINGS;
DROP TABLE IF EXISTS `family` ;
CREATE TABLE `family` (
`parentID` INT(5) NOT NULL ,
`playerID` INT(5) NOT NULL ,
PRIMARY KEY (`parentID`, `playerID`),
FOREIGN KEY (`playerID` )
REFERENCES `player` (`playerID`)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (`parentID`)
REFERENCES `parent` (`parentID`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
SHOW WARNINGS;
If someone can help me sort out this problem by giving some guidance, and even some generic examples with explanations, that would be really good. I think this is a many:many relationship that I just can't drop altogether, as parents can have one or more children and a child can have one or more parents (a family wouldn't really be a family without children in this context).
Many thanks in advance!!!
A child can have no more than 2 parents, both of them have specific roles (mother vs. father) and there can be a situation where one or both parents are unknown.
So this isn't a real "many to many" relationship, it actually "many to zero or one or two", which can be naturally represented like this (both MotherID and FatherID are NULL-able):
If you put LastName in both Parent and Player (or a common superclass in your case), this also naturally covers the situation where parents have different last names from their children.
You can then easily get "children per parent" like this (SQL Fiddle)...
SELECT
ParentID,
Parent.FirstName ParentFirstName,
Parent.LastName ParentLastName,
PlayerID,
Player.FirstName PlayerFirstName,
Player.LastName PlayerLastName
FROM
Parent
LEFT JOIN Player
ON Parent.ParentID = Player.MotherID
OR Parent.ParentID = Player.FatherID
ORDER BY ParentId
...and pivot the data in your application code if that's what you wish.
The above model allows mismatch between Parent's gender and its mother/father role. If you want to prevent this you can go overboard and do something like this...
...but I'd rather not complicate and stick with the first model and enforce this at the application level.
这篇关于需要一些建议和反馈在MySQL中的许多许多关系编码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
