Database Normalization: Always the best thing to do?
I'm working on setting up a new database and was trying to figure out how to do a few things. My question is, is database normalization always the best thing to do or are there exceptions? If I were to do it normalized, it would add roughly 8 to 15 tables to the existing 20 or so I already have. Here's my dilemma:
I have a large table of products (roughly 10,000). Within the contents of this table are columns that would tie into other tables that have the appropriate words or descriptions. This is necessary because it will be multi-lingual.
So for example, I would have a table of items and some of the columns may be:
Products Table: id, name, description, color
Then there would be a color table which would have:
Color Table: id, color_english, color_spanish
So I can have under the Products.color column data listed something like "3, 15, 20" to represent the id's of all the major colors of that product OR I could have some Products_Color table for a more normalized solution but would add a lot of tables when covering all the product columns.
A product can have multiple colors, so either way I have to figure out how to pull the correct data in a query.
In my brief research of mysql database normalization I read on wikipedia that databases intended for online transactions or are updated often tend to be more normalized than databases presenting data collected over time for informational purposes. Mine falls into the latter and it seems more logical to me to do it this way but wanted some outside opinions.
Second question:
In the example above, how would I in a query left join and pull the colors from the correct language column for each of the color id's within a specific cell. For example:
If a product within the product table had "3, 15, 20" for the id's of the colors, in a query for that product pulling its information how could it also get the 3 colors and result in something like "Blue, Green, Charcoal"? Left join would work for 1, but not 3 that I know of. Anything else that would work?
Any help would be greatly appreciated. Thanks!