Hello, I am in a database design dilemma.

In a web application I'm going to make, a table will be used to store colors. The stored colors will be in varying color systems (RGB, HSL, CMYK and LAB and/or HSV might be added later), so an enum field will be used to store the colorsystem. So, my question is, how to store the values?

Here is what I've thought so far about my options (feel free to add another if you think of something better!)

1. Just one int field that will store a combination of the values in some format and a colorsystem-particular formula will be used to extract the components.
Pros: Just one field for the value
Cons: We can't easily search for eg an RGB color with a Red value of 128, too complex

2. Four fields for the relevant components (only CMYK will need all four)
Pros: Easily searchable, very simple
Cons: Not elegant, doesn't feel right

3. All possible fields (Red, green, blue, hue, saturation, lightness, cyan, magenta, yellow, black)
Pros: We can store the color in any format, so that conversions won't be needed to be performed on runtime, easily searchable, fairly obvious
Cons: Too verbose, not good for maintenance (What happens when lots more colorsystems are added?)

This has been troubling me for days, and I can't start to work on the site unless I figure it out. Any ideas?

Two tables.

Table1 is simply Color Names and Type.

create table Colors (
  id int not null
  ,name varchar(16) not null
  ,system tinyint not null
)

'system' maps to a numerical value (1 = RGB, 2 = CMYK, etc)

Table 2 is a table of key:value pairs

create table ColorPairs (
  colorid int not null
  ,key varchar(8) not null
  ,value int not null
)

Example of the color Red in RGB (Red,Green,Blue) notation:

Table 1:
1, "Red", 1

Table 2:
1, "Red", 255
1, "Green",0
1,"Blue",0

I finally decided to go with #3, so it's a bit late now..
Thanks for your time though :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.