MySQL Forums :: Newbie :: Database Normalization: Always the best thing to do?


Advanced Search

Database Normalization: Always the best thing to do?
Posted by: Dude Abides ()
Date: June 29, 2009 08:05PM

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!

Options: ReplyQuote


Subject Written By Posted
Database Normalization: Always the best thing to do? Dude Abides 06/29/2009 08:05PM
Re: Database Normalization: Always the best thing to do? laptop alias 06/30/2009 03:19AM
Re: Database Normalization: Always the best thing to do? Dude Abides 06/30/2009 10:46AM
Re: Database Normalization: Always the best thing to do? Dude Abides 06/30/2009 07:48PM
Re: Database Normalization: Always the best thing to do? laptop alias 07/01/2009 06:59AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.