Lineage tree database design

Question

As the title says, I'm building a cannabis strain genetics/lineage database.

If you didn't know; one cannabis strain may be an original strain (no parents) or it could be the result of many crosses (multiple parents).

Now, when I first started this, I had no knowledge of cannabis strains and genetics. I thought that one cannabis strain could only have two parents...

Example:

StrainX = Strain1 x Strain2

"This is easy", I thought to myself, and proceeded to build what I thought was a good database to suit those requirements. It was simply one table for strains and one lookup table for strainID, parentID.

Example:

strainID  | parentID
-----------------------
1         | 3849
1         | 975

I was soooooo wrong! I then came across a strain (7,075 different strains by the way) that had this genetic makeup:

Example:

StrainZ = {(Strain1 x Strain1) x Strain2)} x {(Strain1 x Strain1) x Strain670}

and I believe there are ones that have more:

Example:

StrainY = {(Strain1 x Strain1) x (Strain2 x Strain2)} x {(Strain1 x Strain1) x (Strain670 x Strain 670)}

If you're as baffled as I am about those crosses, I'll try and explain as much as I have learned.

Let's look at StrainZ. They first crossed Strain1 with Strain1 to make the genetic makeup of Strain1 stronger (maybe it had the best yield), and then crossed it with Strain2. They then crossed Strain1 and Strain1 again to get the strongest characteristics from it, and then crossed that with Strain670 for the other parent. They then crossed those two to make StrainZ.

So, as you can see, my daft little lookup table became a laughing matter, and I think my intelligence has a bit, too.

I'm actually not sure which direction I can take this now, without asking which direction is best.

One idea of mine is to extend the lookup table to not just log one parent next to one strain, but to log all of the parents next to one strain. I'll explain.

Take a look at the most complicated lineage I gave up above:

StrainY = {(Strain1 x Strain1) x (Strain2 x Strain2)} x {(Strain1 x Strain1) x (Strain670 x Strain670)}

With a format like this:

StrainY = {(p1aa x p1ab) x (p1ba x p1bb)} x {(p2aa x p2ab) x (p2ba x p2bb)}

I could change my lookup table to the following:

strainID  | p1aa | p1ab | p1ba | p1bb | p2aa | p2ab | p2ba | p2bb
------------------------------------------------------------------
1         | 3849 | 3849 | 5632 | 5632 | 3849 | 3849 | 890  | 890
2         | 390  |      |      |      | 5302 |      |      | 
3         | 667  |      | 6199 | 6199 | 701

Unfortunately, this is as far as my mind/knowledge goes. I can actually hear short-circuiting inside of my head. Even if this new table design could work, I'm not sure how it would work.

I do think the design above works, I suppose, if I just wanted to show what parents were crossed to create the current strain, and I have the ability to link to the parent's pages with the ID's returned. However, I don't think it's a good design that will allow me to build an efficient SQL query that could list an entire history of the strain, without a performance-crushing number of queries in loops.

If I explain what I'd like to do with the data, maybe it will give somebody a better idea on how to answer.

I need this lineage table for two reasons:

A "related strains" feature. If you're looking at the page of Strain670, I want to show what other strains have Strain670 as parents. Pretty simple.
To show a family tree diagram of sorts; showing parents, grand parents, great grand parents, right back to the start of the lineage. Not so simple. See below family tree diagram for example.

StrainZ - {(Strain1 x Strain1) x Strain2)} x {(Strain1 x Strain1) x Strain670}

(Strain1 x Strain1) x Strain2
    Strain1 x Strain1
        Strain1
            OriginalStrain100
            OriginalStrain101
            OriginalStrain102
            OriginalStrain103
    Strain2
        OriginalStrain104
        OriginalStrain105
        OriginalStrain106
(Strain1 x Strain1) x Strain670
    Strain1 x Strain1
        Strain1
            OriginalStrain100
            OriginalStrain101
            OriginalStrain102
            OriginalStrain103
    Strain670
        OriginalStrain104

Based on that, can you help? Can I get away with the new table structure as explained above (p1aa, p1ab...) or can you suggest a better design?

score 3 · Answer 1 · answered Oct 08 '15 at 18:20

Im not sure on why you want to track the make up of the stains but realistically, you can keep your original design because as stated in your question:

Take a look at the most complicated lineage I gave up above:

StrainY = {(Strain1 x Strain1) x (Strain2 x Strain2)} x {(Strain1 x Strain1) x (Strain670 x Strain670)} {(Strain1 x Strain1) x (Strain2 x Strain2)} STRAIN 1 {(Strain1 x Strain1) x (Strain670 x Strain670)} Strain 2

Still concludes to st1 x st2 = product

An easier way to look at it is this way...

lets say you have the color PURPLE and ORANGE what makes up PURPLE (BLUE X RED) and ORANGE( YELLOW X RED )

TO track what color purple and orange make you only need to see ORANGE X PURPLE. You do not need to track (BLUE X RED) X (YELLOW X RED). As long as you have your groups that you know make up which colors, you should be good to go no matter what combination you have.

To see what ORANGE AND PURPLE you would create you would simply select those colors and combine them, no need to micromanage them, and you already know what orange consist of, and what purple consist of.

Hopefully that helps, again this is just from the info I have gathered, so I wanted to at least contribute based on what I am reading.

GL!

score 1 · Answer 2 · edited May 23 '17 at 12:40

I agree with Hector's assessment. It will probably be more flexible just to keep your original design. The piece that you might have missed to make it work is using recursion to traverse up the tree. I don't know much about recursion in MySQL, but this answer on StackOverflow feels like a good resource.

The biggest problem with your proposed alternate is this will break down as your complicated strains become parent strains. Overtime you will have to keep adding columns and that is an unmaintainable nightmare that no one wants to deal with.

The biggest gotcha that I don't see you addressing is what trait of Strain1 the breeder(?) was trying to strengthen by breeding Strain1 with itself. To handle this you can add extend your original schema to look like:

strainID  | parentID1 | parentID2 | goalID -- Or reasonID, purposeID, etc
------------------------------------------------------------
1         | 3849      |   975     | 1 
2         | 3849      |   975     | 2 -- Same parents as above different objective

goalID | Description
----------------------
   1   | Increase THC content
   2   | Decrease stank

Admittedly this information would be more useful for breeders than consumers but I'm not sure who your target audience is with your application. If you are trying to build descriptions like this:

Don't let its intense name fool you: AK-47 will leave you relaxed and mellow. This sativa-dominant hybrid delivers a steady and long-lasting cerebral buzz that keeps you mentally alert and engaged in creative or social activities. AK-47 mixes Colombian, Mexican, Thai, and Afghani varieties, bringing together a complex blend of flavors and effects. While AK-47’s scent is sour and earthy, its sweet floral notes can only be fully realized in the taste.

... for marketing purposes then your original schema should work fine, and you will have to resort to manually curated descriptions. Either way recursion will be your friend.

Lineage tree database design

I need this lineage table for two reasons:

2 Answers2