Breed Percentage

[carleihar]

I've been thinking about this for a while, but I just can't seem to figure out how to do it. First, I have a SQL database with a list of horses and their unique characteristics. I have made a program that takes information from two horses and uses the information to create a totally new horse. One characteristic I am stuck on is breed. I would like to show the percentage of each breed that is in the horse. So if a 50% Quarter Horse and a 50% Throughbred is combined with a 100% Warmblood, it should show percentages accordingly. Any help?

[corbin]

So you're confused about how to store it, right?  Not the math?  (The math is just half everything.)

 

 

I would probably just put it in a table like so:

 

CREATE TABLE breeds (

    breed_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    breed_name VARCHAR(255)

);

 

CREATE TABLE horse_breeds (

    horse_id INT NOT NULL,

    breed_id INT NOT NULL,

    part TINYINT NOT NULL

);

 

And part would range from 1-100 and represent a percent.

[carleihar]

I guess I'm confused about both. Would I need multiple columns to store each breed and its percentage? I would like to store every percent and its corresponding breed.

[zeodragonzord]

In corbin's example, the horse_breeds table doesn't have a primary key but the horse_id is repeated; multiple rows represent one horse.

 

So let's say HorseA is a breed of 20% of HorseB and 80% of HorseC.  The tables would look like the following (see first two rows of horse_breeds:

 

 

breeds:

{1, HorseB}

{2, HorseC}

{3, HorseD}

{4, HorseE}

 

horse_breeds:

{1, 1, 20}<--{unique identifier for the horse, HorseB, 20%}

{1, 2, 80}<--{unique identifier for the horse, HorseC, 80%}

{2, 2, 50}<--{unique identifier for the second horse, HorseC, 50%}

{2, 3, 10}<--{unique identifier for the second horse, HorseD, 10%}

{2, 4, 40}<--{unique identifier for the second horse, HorseE, 40%}

 

Also, it wouldn't hurt to give the horse a name:  horse_breeds.name