Color Genetics database structure suggestions/help.

[pocobueno1388]

Hello everyone =]

 

I am currently programming a horse color genetics system. There are many breeds of horses, and many different colors of horses. The tricky part is, each breed can only have a certain amount of colors that they come in.

 

I am trying to figure out the best database structure possible. So let me know what you think of this.

 

This will be the horse table, where each individual horses information is stored.[tt]

TABLE "horses"
--------------
horseID
breed
color

 

[tt]This will be a table full of ALL the colors.

TABLE "colors"
---------------
colorID
color (the name of the color)

 

This table will hold a list of ALL the horse breeds, and all possible color combinations using the "colorID" column of the "colors" table.

TABLE "breed_colors"
--------------------
breedID
breed (name of breed)
colors (This will hold a list of all possible color possiblities from the "colors" table.)

 

An example of what the "colors" row would hold in the "breed_colors" table would be something like this:

2,10,26,52,70

This would mean that breed could come out with those possible colors corresponding to the "colorID" in the table "colors".

 

Does this sound like it would be a good approach to the database design/idea of what I'm doing? If you have any suggestions to a better structure, please share your thoughts

 

Thank you all very much, I appreciate the time your taking to help me

[Eric_Ryk]

Create another table, which looks like so:

 

breeds_colors

breedID

colorID

 

Then change the breed table to just:

breedID

breed

 

That's basically the "proper" way to set up a many to many relationship.

[pocobueno1388]

Your right, that would probably be easier, I spaced that out for some reason =] Thanks for the correction.

 

Any other suggestions?

[pocobueno1388]

bump

[Eric_Ryk]

With a structure that simple there really isn't much else you can do to make it better.