Need some advice and feedback on coding a many:many relationship in MySQL

Question

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!!!

Answer 1

ok this is too painful. You are way over complicating it. I don't have visio so try and cope:

• PERSON(person_id, first_name, last_name) PK: person_id
• FAMILY(parent,player) PK:(parent,player) FK:FAMILY.parent -> PERSON.person_id, FK:player->PERSON.person_id

Thats it - simple.

All you need is a table which matches two different people together. To find all the caregivers of a kid you just join person to person and filter for the child.

For address:

• PERSON_ADDRESS(person_id,address_id) PK:(person_id,address_id) FK:person_id->person.person_id, FK:address_id->address.address_id
• ADDRESS(address_id,st-num,street, city,phone) PK:address_id where st-num may be alphanumeric for things like 23B

Actually - I'm not comfortable with you putting phone in address. Doesn't scale to multiple phone numbers in an address. But address is a distraction. Your problem seems to be about linking players to parents.

School should be it's own table. Either put the school_id into person or if you hate nulls then have a joining table person_school which only has records for the kids

Answer 2

I'm not sure why a person would have multiple addresses - but I guess they could. However if I was doing multiple addresses then I'd push the address into another table and have a person_address joining table. That way you don't end up repeating the address data for each and every person who lives in the same address.

Your parent table needs two fields - one for the parent, the other for the player (as I said earlier BOTH I think itd work better if they both pointed to person). Why have you got two relationship lines between paren and player?

The person|parent relationship will NOT be 1..1 - 1..1 because that means every single person is a parent, your player probably isn't.

Just an aside - but you really do seem fixated on a mother and father. There may be none of either and multiple of either. You don't need separate relationships stipulating the gender - that should either be data in PERSON or not in there at all as irrelevant.

Answer 3

Am not sure if this is an "answer" per se, but this is my attempt at adjusting my MySQL code to fit in with what Branko Dimitrijevic's answer provided me. Here is my adjusted MySQL code for the tables concerned:

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,
  PRIMARY KEY (`parentID`), 
  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,
    `motherID` INT(5),
    `fatherID` INT(5),
    `schoolID` INT(5),
    PRIMARY KEY (`playerID`), 
    FOREIGN KEY (`playerID`) REFERENCES `person` (`personID`) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE,
    FOREIGN KEY (`motherID`) REFERENCES `parent` (`parentID`) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE,
    FOREIGN KEY (`fatherID`) REFERENCES `parent` (`parentID`) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE, 
    FOREIGN KEY (`schoolID`) REFERENCES `school` (`schoolID`) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE)
ENGINE = InnoDB;
SHOW WARNINGS;

And this is the part of the logical model with these tables:

If Branko, or anyone else, could verify my attempts with Branko's answer that would be good. Also you will notice that for the image I am using MySQL Workbench for the model.

The following is out of scope for this particular problem, but if anyone can help with this also that would be great:

I have been asked to not use reverse or forward engineering to convert sql code to model schema etc, but this enabled me to verify the model I had constructed in Visio for correctness. However I cannot quite get the Visio model to look like the Workbench model - here is the Visio version of the tables I am trying to model as above:

As you can see in the Visio version, the "fatherID" and the "motherID" columns are not indicated as foreign keys, which is what I am wanting (and is illustrated in the Workbench version). If someone can help me get my Visio model looking identical to the Workbench version that would be good.

Answer 4

All you need is a joining table with two fields: kid, caregiver. That's essentially what you have for your "family" table - I don't understand the purpose of your "parent" table. BOTH values in "family" will be foreign keys to "person" (ie making 'player' a more generic table than you have in your diagram.)

That then allows any kid to have any number of caregivers. And any caregiver to be linked to any number of kids. All possible combinations of perverse and wonderful family arrangements are accommodated.

Answer 5

I'd question the use of the family entity at all. A child can be a member of two families by virtue of a divorce and subsequent remarriage of parents -- how would you model that? Might a child not have four persons effectively acting "in loco parentis"?

Answer 6

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.

Answer 7

The Family table, which implements the many-to-many relationship is usually created after the Parent/Player(s) data has been entered by the user. The UI typically would be depicted as a Main-Form/Sub-Form combination where the Parent is entered/selected from the Main-Form and one or more Players associated with the Parent are entered/displayed in the Sub-Form; usually a grid of some sort.