DB Design Help - Many colors

[MMProject]

I'm a school teacher and novice at this, but am trying to design a DB for kids to input the M&Ms, Skittles, Etc for a long-term probability exercise...

As each bag can consist of multiple colors of a predefined list (about 36 colors), I wanted to allow for logging of each bag (type of candy, color, variety)… I envision a dropdown for the type of candy and series of checkboxes (for each color by variety). Although most of the data is easy to put into a DB and query, the color thing really gets me as there can be any number of colors per bag (up to 36)… What is the best way to design the DB to account for 36 color options for each variety and be able to report just the colors that were in the bag?

To make it even harder, when searching I’d like to have the option for a search to identify all shades of a color (color family), so an advanced search would allow identification of every bag that contained a form of blue (Aqua, Cyan, Light Blue, etc) and a search for green would return all forms of green (including Aqua). In other words, colors could belong in up to 2 color families.


Example: Ability to Browse Bags
----------------------------------------------------------------
Bag: 1
Student: John
Type: M&M
Plain Colors: Red, Yellow, Blue, Brown
Peanut Colors: Red, Orange, Yellow, Green
Dark Colors: Red, Purple, Green

Bag: 2
Student: Lucy
Type: Skittles
Plain Skittles: Red, Yellow, Green, Purple
Sour Skittles: Light Green, Cyan, Pink
Tropical: Aqua, Teal, Light Orange



Example: Ability to Search Bags by Type, Variety, and Color
----------------------------------------------------------------
Type: M&M
Variety: Plain
Color: Red

Results:
Bag: 1
Bag: 4
Bag: 9

[traq]

do you know in advance all colors that may be in your bags? if so, just create a field for each color in your DB. Colors that weren't in the bag would remain NULL (or zero).

Are you using MySQL or another database? Independently or with another programming language for the interface (PHP, etc.)?

[djr33]

Are these "bags" generated or entered manually? Entering them manually seems very simple, but if they are generated it will be a little more complex.
Regardless, you should be able to just setup a normal table with a few columns.
You can use LIKE '%blue%' to search in the 'colors' data for any that contains 'blue' or perhaps better as 'blue,' so it is in a list format.
Since you are not using related words like "light.blue" and "dark.blue", and some may belong to more than one color family, then I would say you should just create a manual list (array) of "blue" colors and then search the database for any matches to any of those.

[MMProject]

The bags are real and to be entered and queried by the students. All of the colors are predefined, so I can create a table with boolean column for each, but as each bag may need to refer to the color table up to six times (m&m, skittles, etc.) per bag... I was confused on how to link this, or if I would have six different color tables. Then I'm not quite sure how to get the results to report correctly (red, yellow, blue) when I will only have a yes/no result to query. Or is there some kind way to report the column name if boolean = true. I was planning on programing in c# or ASP, but could probably do PHP if that were easier.

As each color does not have the color names in them (aqua), I cannot query LIKE '%blue%', and if I were to use booleans for the colors I don't know how I would search against column names at all...

[MMProject]

My plan was for an access DB (I know it is not the most dynamic, but would not require a server)

[djr33]

Here's how I would set this up:

bag id # | [candy type?] | colors
     1         skittles     red,red,blue,red,green....
    34         M&Ms         blue, blue, red, aqua

In your programming language, define a list (or even in the database if you need to):
$blues = array('blue','aqua','light blue','navy',.........);

Then query against the database for each of those colors; if any are present then a "blue" is there.

Everything else can be done in the same way.


If you want to set things up in a different way it's probably fine too, but you will need some sort of bag identifier (#, student's name, etc) and a list of colors in the bag. You could instead approach it in two other ways, but they seem more complex:
1. Have a column in each row for each color:
id#|blue|red|green
1--|20--|8--|6
2. Have a "candies" table instead of "bags" table and list every single candy individually, by bag id (that it is in) and by color.
Of course that seems messy, but if you want to do complex things like maybe later switch M&Ms from one bag to the other it would be possibly helpful.

I'd suggest just keeping it simple: bags with a list of colors in them.

Parse as needed... string functions can get complex, but probably easiest way overall.

The most efficient way may be to use idea (1) above if you are using a constant number/names of colors.