MySQL - Best column type?
Lets say I wanted to store color type... lets just say, in theory... 50 colors to choose from.
What would be the best way to store it? here is what I have thought of:
- A varchar with the actual value...
- An integer which I would convert to the value
- ENUM.. I am not too familiar with this, it says it can store 65,535 values.. but it just seems a bit "odd" to me?
What would be the best way, to do this? If there is a better way I have not listed (thought of) please let me know!
Thanks.
SonicMailer Pro
The best mailing list manager has just gotten better!
Click here for a full list of features!
druagord posted this at 21:34 — 30th July 2003.
He has: 335 posts
Joined: May 2003
i usually use a char(6) and store the color in ffffff format
Mark Hensler posted this at 21:38 — 30th July 2003.
He has: 4,048 posts
Joined: Aug 2000
What is this going to be used in? Are you looking for speed? To conserve space? Or simplicity in design?
nitin posted this at 06:17 — 7th August 2003.
They have: 4 posts
Joined: Aug 2003
Looking at your requirement, you may be just fine with the following DDL
CREATE TABLE COLOR(
COLOR_ID TINYINT UNSIGNED NOT NULL,
COLOR_NAME VARCHAR(20),
PRIMARY KEY(COLOR_ID)
)
TINYINT should take you 255 color ids. If you think you need more, you can always alter the table to use MEDIUMINT.
Hope that helps,
- Nitin
Webmaster tools, tutorials and tips.
Get a FREE ebook on Building Dynamic Web Sites
technobuff.net
Mark Hensler posted this at 07:39 — 7th August 2003.
He has: 4,048 posts
Joined: Aug 2000
MEDIUMINT is insane (24 bit), SMALLINT should be all you need (16bit). Unless we're going for 32bit True Color with an INT field.
nitin posted this at 04:14 — 8th August 2003.
They have: 4 posts
Joined: Aug 2003
Hi Mark,
With all due respect to your thoughts, I believe the point is not about storing color codes. The question was only about storing 50 colors and if we are talking about websites, it's always better to choose web-safe colors. So, again using a name is a much better option than using color codes directly. That's just the standard web-design practice every experienced webmaster follows.
If you strictly want to design your table for codes, IMHO, then probably a better option is to use UNSIGNED INTEGER would be a better option.
Thanks,
- Nitin
Webmaster tools, tutorials and tips.
Get a FREE ebook on Building Dynamic Web Sites
technobuff.net
KandieMan101 posted this at 04:16 — 8th August 2003.
They have: 140 posts
Joined: Jan 2003
Could you please show me a large, well developed site that uses color names INSTEAD of color codes?
SonicMailer Pro
The best mailing list manager has just gotten better!
Click here for a full list of features!
Suzanne posted this at 04:34 — 8th August 2003.
She has: 5,507 posts
Joined: Feb 2000
Why on earth would you not use hex/rgb instead of names? Names aren't even fully supported across the board?
I'll use the basics (black, white, blue, red, yellow, green, orange and purple) on occasion, but otherwise, it's hex/rgb in the CSS.
That said, if we're talking about product colours (i.e. the khaki sweater, or the kelly green sweater), then colour names are better. For that application, a table that only lists colour code and colour name would be best.
Mark Hensler posted this at 05:59 — 8th August 2003.
He has: 4,048 posts
Joined: Aug 2000
Hey nitin, I was remarking on your comment of enlarging the primary key from TINYINT to MEDIUMINT. I wouldn't expect anyone to store colors in an integer field unless they want a headache.
Mark Hensler
If there is no answer on Google, then there is no question.
m3rajk posted this at 18:44 — 10th August 2003.
They have: 461 posts
Joined: Jul 2003
i read soomewhere that the percent and decimal versions are less stable than the hex codes. so why would someone making a large site use anything but hex?
and for hex all you need is a char(6)
just use the fully qualified hex: '000000' through 'ffffff'
POSIX. because a stable os that doesn't have memory leaks and isn't buggy is always good.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.