How could I store a color in a database field?

Solution 1:

If its for a HTML Page, storing the #RRGGBB tag as a string is probably enough.

If its for .NET , it supports building a color from its ARGB Value

System.Drawing.Color c = System.Drawing.Color.FromArgb(int);

int x = c.ToArgb();

so you could just store that int.

Solution 2:

The ideal storage format depends on how you plan to use the database.

The simplest solution is of course just storing everything as a 6-byte ASCII hex string of the RGB color, without support for any other format. Though you may run into issues if you later wanted to support additional formats.

For readability, flexibility, and ease of access, using a plain string is a good idea. The difference in storage space between a hex color string and a raw integer is negligible in most cases. For a speed boost you can set the color field to be indexed. And for flexibility, you could add one or more of the following features:

• Assume the contextual default color if NULL, blank, or invalid
• Accept an optional trailing alpha byte 00-FF, assuming FF (opaque) if omitted
• Accept both full (AABBCC) and shorthand (ABC) syntax, which is half the size, faster to type, and supported by CSS
• Support an optional leading # digit, which is common
• Support raw strings, to hold anything CSS supports like "rgba(255,255,255,1)" or "red"
• Support custom color mode strings like "cmyk(), hsv(), hsl(), lab()", etc.
• Assume RGB(A) hex strings if it begins with a # or the length is 3, 4, 6, or 8 and contains only [0-9A-Fa-f]

To optimize search and sort speed, as well as disk use, storing as an unsigned integer is the way to go. This is because a single number is faster to search through than a string of characters, can be stored internally as small as a few bits, and you can still filter by color channels in your queries using FromArgb() and similar functions. The downside is your code then needs to constantly convert things back and forth for every color field in every query, which may actually offset any database speed gain.

A hybrid approach may be worth exploring. For example, consider a table of all possible 8-bit-per-channel RGB values, with fields composed of things like id, rgbhex, cssname, cmyk, hsl, hsv, lab, rgb, etc. You'd need to automate the creation of such a table since it would be so large (16777216 entries). It would add over 16 MB to your table, but the advantage to this solution is that all your color values would just be a single integer id field linked with a foreign key to the color lookup table. Fast sorts and searches, any color data you need without any conversion, and extremely extensible. You could also keep the table in its own database file to be shared by any other database or script in your application. Admittedly this solution is overkill for most cases.

Solution 3:

Probably the colour value would be best, e.g. #FFFFFF or #FF0000

Solution 4:

Store a colour as a 24 or 32 bit integer, like in HTML/CSS i.e. #FF00CC but converted to an integer not a string.

Integers will take up less space then strings (especially VCHARs).