Results 1 to 6 of 6

Thread: DB Design Help - Many colors

  1. #1
    Join Date
    Feb 2010
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default DB Design Help - Many colors

    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

  2. #2
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 517 Times in 503 Posts
    Blog Entries
    5

    Default

    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.)?

  3. #3
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,162
    Thanks
    263
    Thanked 690 Times in 678 Posts

    Default

    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.
    Daniel - Freelance Web Design | <?php?> | <html>| español | Deutsch | italiano | português | català | un peu de français | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  4. #4
    Join Date
    Feb 2010
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    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...

  5. #5
    Join Date
    Feb 2010
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

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

  6. #6
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,162
    Thanks
    263
    Thanked 690 Times in 678 Posts

    Default

    Here's how I would set this up:


    Code:
    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.
    Daniel - Freelance Web Design | <?php?> | <html>| español | Deutsch | italiano | português | català | un peu de français | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •