Thread: Mathematical Solution?

Hello,

New to the forums not sure if this is the correct place but here goes...

I am wondering if there might be a mathematical solution to my problem. I am setting up a breeding program and have a database with "animal id", "mother id", "father id"

I have a job that allows the user to select (for example) 80 females and 8 males to breed, each male would be assigned 8 females to breed with.

I also have a couple rules for the breeding assignment, those rules are...

Brother/Sister mating is NOT allowed.

X number of sisters (maximum) can breed with the same male.


I can not control the animals that the user selects though. Right now my job try's to randomly assign 8 females to each male while avoiding the limitations. If it fails to place 100% it just loops and try's again.

Is there some sort of way that I can determine if the placement is possible or not with a combination of SQL and some clever math?

Thanks

Attached is some sample data..

I understand the problem and think an SQL code would exist (or could be written easily enough) but would exactly do you want your output to show? I imagine a table with all the males along the top and females down one side. The inner part of the table could be populated with a 1 or 0 given the couple is compatible?

Can step brothers and sisters mate?

I'm thinking a pivot table could work for this as well if you add some field(s) with a 'couple' id.

Half brother/sister should also not be mated.

Ideally the result table would have a male id and female id column (the number of rows equal to the total number of females placed with a male id repeated, for instance 8 times for each grouping).

I have written a script that will create a result table but sometimes it isn't possible to place 100% with the supplied values. The current script I have takes the following steps...

Picks a random male.
Assigns 1 female to the male in accordance to the rules.
Picks a different random male.
Assigns 1 female to the male in accordance to the rules.
Picks a diffrent random male.
..
..
..
Once all males have at least one female assigned it picks a random male and assigns a second female.
After that it picks a random male (with one female assigned) and assigned another second female.

And so on....

The problem I am having is that sometimes the user selects a group of male/females where it is just impossible to place them all. In this case I don't know if perhaps it would place them all in a different way so I just roll the dice again and hope that it works.