Trying to understand database normalization - 3NF

Question

I am trying to understand database normalization, in particular 3NF.

I am building a shop database and in trying to normalize to 3NF have come up with the structure below.

The structure below assumes

There may be multiple categories per product
There is only ever one storage_location & manufacturer per product
There may be unlimited extra_fields

Can anybody tell me if I am on the right track, is the below structure in 3NF, or close?

Products
----------------
pid (pk)
title
desc
price
weight_base
weight_additional
note
quantity
manufacturer_id (fk)
storage_location_id (fk)

Categories
-----------------
category_id (pk)
category_name
parent_id
description

Product_categories
-----------------
pid (pk)
category_id (pk)

Manufacturers
-------------------
manufacturer_id (pk)
name
description

Storage_locations
---------------------

storage_location_id (pk)
storage_ref
storage_note

Product_extra_field_values
-----------------------------------

PID (pk)
extra_id (fk)
value

Product_extra_fields
--------------------------------

extra_id (pk)
label

Two main things I am unsure about:

Is it correct to use a composite key for Product_categories to accommodate for multiple identical PIDs in the table?
Is it correct to simply add a foreign key to the primary Products table for manufacturer and storage_location because they will only occur once per product record? Or should specific manufacturer and storage_location id be removed from primary Products table and new tables be created like this:
```
Product_storage_locations
----------------------------------
PID (pk)
storage_location_id (pk) 
```

Dmitry B. · Answer 1 · 2011-11-23T05:58:35.457

1

by using three tables -- product, category, product_category -- you are setting up a many-to-many relationship between products and categories. I.e. any one product can belong to multiple categories and any one category can be assigned to multiple products. If that's not the intention, then this isn't the right design.

Conversely, if you have a one-to-many relationship, then you only need two tables. In this setup the "many" table entries get to hold the foreign key into the "one" table. It looks like your design is correct as long as one-to-many is what you want for the product-manufacturer relationship. If you want many-to-many, you'll need a three-table design.

~~BTW, both fields in the product_category table are foreign keys (fk), rather than primary keys as you indicated.~~

edited Nov 23 '11 at 05:58

answered Nov 23 '11 at 05:28

Dmitry B.

9,107
3
43
64

Your note down the bottom is not quite true - they are Foreign Keys into other tables, yes; but they also make up the Primary Key themselves. Every table needs a primary key; and in this case, since multiple categories can apply to individual products; and multiple products can apply to individual categories, neither is unique on its own, so you *do* need to have both, together, as the primary key. – Smashery Nov 23 '11 at 05:38
i was looking at that differently, but i see what you mean – Dmitry B. Nov 23 '11 at 05:57

Smashery · Accepted Answer · 2011-11-23T05:43:42.383

Yes, what you have looks correct to me. What you've done with putting product storage location and manufacturer in the Products table is absolutely correct. If you put it in a separate table (down the bottom), you'll just be creating more work for yourself with all the joins you'll have to do later.

The only reason you would split that out into a new Product_storage_locations table is if a product could be in multiple locations (which, according to what you've said, is not the case). However, in the case of products and categories, that is what you want to do - and that's what you've done with the Product_categories table; and is absolutely the right thing to do.

The only thing I'm not sure about in your design is how you've done extra fields. Without more information about your intent, it's hard to say whether what you've done is right or not. It looks to me, though, that what you have is a particular type of field which may apply to many products; but whereby each of those products may have a different value for that field. If that is your intent, then that design looks fine.

Thanks for the answer - I was thinking of using the extra_field to dynamically add extra optional product data such as condition (new or used). However thinking about it Product_extra_field_values would be full of identical values in the case of the above example i.e. 'new' or 'used', so perhaps it would be better to create a third extra_field table eg 'Product_extra_field_value_options' that would have a value_option_id and value_option_value and then instead of 'value' in the Product_extra_field_values table have value_option_id — someuser, Nov 23 '11 at 13:21

Trying to understand database normalization - 3NF

2 Answers2