Best way to represent a color in a SQL Database?

Question

If I am using .Net and SQL Server 2008, what is the best way for me to store a color in the database, should I use ToString or convert it to an integer, or something else?

Edit:

All I want from the colour is to be able to retrieve it and draw something on the screen in the specified colour. I don't need to be able to query against it.

Answer 1

How are the colors stored natively?

If you're just using 0xRRGGBB format, you may as well store it as an integer in the database, and re-hexify it when you SELECT (for readability).

Answer 2

Are you using the colors to style something or are you storing how many blue items you have?

Answer 3

How to store information in a database depends on how you intend to use and access it. There's no saying what the best way to store it is without knowing how you'll use it.

Answer 4

Color can be surprisingly tricky on some industries like digital cameras, desktop publishing, sanners and other. Most programmers associate color with the 24 bit color (RGB usually), some associate it with the 32 bit (RGBA). The few that work in industries like DTP that make heavy use of color have a richer set of terms that includes color correction, color space and so on and so forth. So what exactly do you need to store?

• Do you need to store a single format that is not going to change?
• Do you need to store multiple formats and know what format is actually stored (RGB vs. RGBA vs. CMY vs. HSV etv) ? Note that even something apparently as simple as RGB actually can be Adobe RGB or sRGB.
• Do you need to store color space and correction? Note often the RGB color has no meaning w/o proper color management.
• Do you need to store a simple text description ('red', 'lime', 'teal' etc) ?
• Do you need to store the 'web' color (ie. RGB or RGBA as hex) ?

Answer 5

Well, I'd store them as six digit hex codes. That opens up the interesting possibility of actually searching for colors between other colors. If you really want to make it powerful, keep the R, G, B hex numbers in three columns. That lets you find colors which contain so much red, or ones that are similar to another color (sort by average difference of the three values).