Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
leaverou
Starting Member
3 Posts |
 Posted - 2009-03-19 : 21:44:48
|
| 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 valueCons: We can't easily search for eg an RGB color with a Red value of 128, too complex2. Four fields for the relevant components (only CMYK will need all four)Pros: Easily searchable, very simpleCons: Not elegant, doesn't feel right3. 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 obviousCons: 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? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-03-19 : 22:47:48
|
#1 isn't so hard to extract color info, you can pull them all using computed columns, something like this:CREATE TABLE Colors (Cspace char(1) not null, Color binary(4) not null, Red AS CASE WHEN Cspace = 'R' then CAST(Substring(Color,1,1) as tinyint) end,Green AS CASE WHEN Cspace = 'R' then CAST(Substring(Color,2,1) as tinyint) end,Blue AS CASE WHEN Cspace = 'R' then CAST(Substring(Color,3,1) as tinyint) end,Cyan AS CASE WHEN Cspace = 'C' then CAST(Substring(Color,1,1) as tinyint) end,Magenta AS CASE WHEN Cspace = 'C' then CAST(Substring(Color,2,1) as tinyint) end,Yellow AS CASE WHEN Cspace = 'C' then CAST(Substring(Color,3,1) as tinyint) end ,Black AS CASE WHEN Cspace = 'C' then CAST(Substring(Color,4,1) as tinyint) end) Although it doesn't really matter, I'd suggest using binary(4) instead of int, it avoids issues with negative numbers. You can extend the computed columns to support HSL and the others, and change the colorspace column of course. Don't forget you can index the computed columns for quick searching too, and in SQL 2008 you can have filtered indexes to exclude nulls for non-relevant colorspaces. |
 |
|
|
leaverou
Starting Member
3 Posts |
Posted - 2009-03-19 : 22:56:37
|
| This sounds too performance intensive, is it?That table will be the most frequently used table in the whole db and its expected to be the biggest one too. |
|
|
|
leaverou
Starting Member
3 Posts |
Posted - 2009-03-19 : 22:57:42
|
| Oh my, where are my manners (and the edit button)?I forgot to add: Thanks a lot for taking the time to help me by the way! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-03-19 : 23:13:38
|
No, it's not likely to be performance intensive, certainly no more than storing all the colors in separate columns. Using the extra space would involve more I/O to read large amounts of data. If your application is only going to read a few rows at most then it won't matter either way. And if you do index the computed columns, then the query optimizer will just do index seeks and probably not even touch the table. Again, it won't matter if one or a few rows will be returned, since SQL Server performs I/O in 64K extents. The benefit in using a single column is storage efficiency, which reduces the need for physical I/O.As far as evaluating the substring and cast, it's trivial anyway, and all of the parameters are constant. That's another reason for using binary instead of int, you don't need an implicit conversion to pull a single byte from it. Doing something like this in C/C++ is probably 1 or 2 dozen CPU cycles, if that, and SQL Server won't be vastly less efficient than that.Best thing to do is test it of course, I'd be really surprised if you even notice performance (except if it's fast)  |
|
|
|
|
|
|
|
|
Storing colors