Normalization

[tharden3]

I've been practicing MySQL and PHP to database products on a website. One of the big problems I'm having is deciding how much normalization I need to do. How much should I break down the product information?

I think for these manufactured goods that I'm data-basing, I really only need one table for each type. For instance:
A table for "air registers" that includes:
- Product name (product type)
- Product Number
- Description

A table for "light fixtures":
- Product name (product type)
- Product Number
- Description

Is this ok? In the O'Reilly book I'm reading they put big emphasis on data structure and good normalization. It kind of seems like I'll figure out what works and what doesn't by succeeding and failing, but I was just wondering if anyone has any suggestions.

[Dormilich]

maybe you should ask this in the database forums....

[nathj]

Based on the outline you have given I would use 2 tables, one to hold product categories listing things like registers and light fixtures. The second table would list all the products and have a field call product_categor y that is an integer foreign key on the categories table.

This structure - a one-to-many relationship - holds up will if one item is only ever going to be in one category enabling one category to hold many items. If however, you want to have the ability to load one item into many categories and and one category to hold many items you need a many-to-many relationship. This typically uses 3 tables, a table for the item a table for the category and table in the middle known as a link table that lists the id's from the other two.

That is not very clear so read on. Item ID 1 is in categories 1 and 2. Category 1 holds items 1 and 2. In this case the link table would be like:

CatID ItemID
1 1
1 2
2 1

Hopefully that is slightly clearer than a muddy puddle.

The question you need to address is what is the relationship between category and product.

Cheers
nathj

[Atli]

There is a generally accepted minimum normalization when designing a database: the 3 normal forms.
Check out Mary's http://bytes.com/topic/access/insigh...ble-structures article for more details on that.

The things I always try to avoid are:

• Putting more than one piece of data into a single field
  That is, if you had a name "John Doe", you may be tempted to put this into a field called "Name". The problem is, the name consists of two parts. A first and a last name. Each of those parts should be stored separately.
  If you store the name as a whole... how are you going to search for all names sharing a last name? It's *practically* impossible.
• Creating multiple fields for the same type of data
  Meaning, if you had a list of people, each of them storing multiple phone-numbers. You might be tempted to create your table like so:
  

  Usertable
  --------------
  UserID Int PK
  UserName Varchar
  Phone1 Char(7)
  Phone2 Char(7)
  Phone3 Char(7)

  The problem here is, what happens if a user needs to store 4 numbers? Are you going to keep adding columns for each number?
  
  No, what you do is take the numbers and put them into a second table, where each row has a UserID and a number. That way each user can have as many numbers as he likes, without the need to alter the actual table structure each tiem.
• Don't design you tables in such a way that requires you to alter their design on the fly.
  By that I mean, if your design would require you to change or add tables to accommodate new data, they are not properly designed.
  
  For example, if you had a eCommerce site, and you had two product categories: "Cars" and "Bikes". You may be tempted to create two separate tables for each of them, each containing their respective products.
  But what happens if the client decides to start selling Motorcycles as well? and then Airplains? And then <insert product>?
  You would have to add a new table for each new category.
  
  What you should be doing: creating one table that contains all the product categories, and another that contains the actual products. The product table would have a column linking each product to a group. (This is a typical one-to-many relationship, 1:N)
  

  -- Category table
  +------------+--------------+
  | CategoryID | CategoryName |
  +------------+--------------+
  | 1          | Bike         |
  | 2          | Car          |
  +------------+--------------+
  -- Product table
  +-----------+--------------+------------+
  | ProductID | ProductName  | CategoryID |
  +-----------+--------------+------------+
  | 1         | MountainBike | 1          |
  | 2         | Ferrari      | 2          |
  | 3         | BMW          | 2          |
  +-----------+--------------+------------+

  This could even be expanded to allow each item to belong to a number of groups. (As explained by nathj).
  (This is typically a many-to-many relationship: N:M)
  

  -- Category table
  +------------+--------------+
  | CategoryID | CategoryName |
  +------------+--------------+
  | 1          | Bike         |
  | 2          | Car          |
  +------------+--------------+
  
  -- Product table
  +-----------+--------------+
  | ProductID | ProductName  |
  +-----------+--------------+
  | 1         | MountainBike |
  | 2         | Ferrari      |
  | 3         | BMW          |
  +-----------+--------------+
  
  -- ProductCategory table
  +-----------+------------+
  | ProductID | CategoryID |
  +-----------+------------+
  | 1         | 1          |
  | 2         | 2          |
  | 3         | 1          |
  | 3         | 2          |
  +-----------+------------+

  Here the "BMW" product is linked to both the "Car" and "Bike" categories. (Not that I expect that BMW actually sells bikes :P)

P.S.
Wow... this post turned out to be much larger than I originally planned it to be :P

P.P.S.
I moved this thread into the MySQL forum. It doesn't really have much to do with PHP.

[nathj]

Atli - excellently put. I just wanted to say that BMW do make bikes so a perfect example.

nathj

[tharden3]

Wow, awesome help. This makes things a lot clearer. Thank you.