deathmetal

Haha, I totally understand. It's glad to know it wouldn't be that much of a problem! I imagine you could also organize the foundation list by 'litter' instead of by individual animal id, so full siblings would not need to duplicate on the data and hopefully minimize the size even more. Thanks for the input 😄

@juddaMy concern is more along the lines of how many entries would be required for a single animal, since the more generations there are, the number of unique foundation animals required grows exponentially - even a generation 4 animal would need 16 entries in a database to keep track of only related foundations (simplified answer assuming no inbreeding and you breed animals of the same generation together). Much of the game is also centered around bartering with other players and making it purposefully harder to accomplish your goals unless you outcross your lines, so it'd be common to have animals with a much higher generation than 4 as the generation number grows with more and more people breeding. And yeah, the separate genetics table is a really good idea. It'd be really nice to be able to mass update genetics or be able to add new alleles/locii without having to mess with the table schema.

Thank you @judda! That does seem like a good method, keeping track of which animals are related to which foundation animals to reference before going through an entire script. Do you think it would get unruly after many generations of breeding? Such as if you had a generation 50 animal, would you keep track of every generation 1 ancestor in that entire lineage? I may have to end up capping it at something like 20 generations/degrees of separation to account for and keep track of, because it sounds like it would be really messy if every single animal had hundreds and hundreds of entries in a database just to keep track of all the foundation animals involved. Right now with the current plan of the game, anyone can create a foundation animal at any time, but it may be worth looking into ways to cap this globally so there aren't potentially hundreds of thousands of foundations that could be accounted for. (or would this be as much of an issue as I'm thinking it could be??) And the genetics table is a good insight that I previously overlooked (I'm not as well versed in database design). The current way it's set up involves a large number of columns to determine things like the alleles and non-mendelian inheritable traits (stuff like specific shades or markings which don't take a lot of space to store), which I thought would be fine since this data is almost entirely tinyint, but it does make sense to separate it from the commonly accessed data, since the genetic information is most commonly referenced in generating images and breeding.

Hey all, I'm currently working on programming a game in PHP (in Laravel 9) that features an extensive and realistic breeding system. One of the most important aspects about such a system, in my eyes, is the implementation and handling of inbreeding in a manner that reflects reality. I consider myself to be well versed in genetics, and I don't need help in terms of actually calculating the COI as I will probably implement a simpler but mostly identical system for the purpose of its existence in my game (AVK, Ancestor Loss Coefficient), however, I'm curious as to how others would tackle an issue I'm currently facing. The calculation requires an array of all the ancestors of the animal, but I'm not sure what the best way to implement this would be. In my ideal scenario, the number would be calculated across an animal's entire pedigree, even if it's hundreds of generations long, but that doesn't sound like it'd be manageable, so I may have to settle on capping it for a specific number of generations to factor in for the calculation. Assuming that the table contains 3 relevant fields, animal_id, sire_id, and dam_id, what is the best way to retrieve the entire list of ancestors for a particular animal in Laravel-y way? I can't come up with any solutions that wouldn't involve doing potentially hundreds of queries in rapid succession-would that be a performance killer? Maybe there's a way I can alter the animal table in a way where it includes more information on the ancestors so it doesn't need to be as many queries, but the thought of doing so many queries at once still sounds awful, and I'd like to keep the animals table as small as possible since it's already much larger with the genetic encoding system. Any help or advice is appreciated, since my knowledge on designing such complicated tasks for scale is super limited.