CODEWITHSUNDEEP
mysqldatabasenormalization
Izanagi
Izanagi

Reputation: 526

Is it worth normalizing?

I am studying about databases and I have encountered this question.If I have for example the table product_supply which containts Invoice_Id(pk),Product_Id(pk),Date_Of_Supply,Quantity and Value_Of_Product.

   | Invoice_ID | Product_ID | Date_Of_Supply | Quantity | Value_Of_Product |
   -------------------------------------------------------------------------
   | AA111111111|      5001  | 08-07-2013     |     50   |       200$       |
   | AA111111111|      5002  | 08-07-2013     |     20   |       300$       |
   | BB222222222|      5003  | 10-09-2013     |     70   |        50$       |
   | CC333333333|      5004  | 15-10-2013     |     100  |        40$       |
   | CC333333333|      5005  | 15-10-2013     |     70   |        25$       |
   | CC333333333|      5006  | 15-10-2013     |     100  |        30$       |

As we Can see The table is already in the 1NF form.My question here is.In terms of normalization if it is wise to normalize this table to a 2NF form and have another table for example supply_date with Invoice_ID(pk) and Date_Of_Supply or if having the upper table is ok?

    | Invoice_ID | Date_Of_Supply |
    -------------------------------
    |AA111111111 |   08-07-2013   |
    |BB222222222 |   10-09-2013   |
    |CC333333333 |   15-10-2013   |

Upvotes: 1

Views: 81

Answers (2)

Ted Hopp
Ted Hopp

Reputation: 234795

It's definitely worth normalizing. If you need to modify a supply date, with 1NF, you need to update several records; with 2NF, you only need to update one record. Also, note the redundancy of data in 1NF, where the supply date is stored multiple times for each invoice id. Not only does it waste space, it makes it harder to process a query like "list all invoices that were supplied between dates X and Y".

EDIT

As Robert Harvey points out in his comments (which it took me a while to understand because I was being thick for some reason), if you already have a table that has a single row for each Invoice_ID (say, an "invoice table"), then you should probably add a column for Date_Of_Supply to that table rather than create a new table.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521209

Changing the table to second normal form involves removing redundancies in the first normal form table. The first question is to determine whether there are even any redundancies.

If a redundancy exists, then we should be able to create a second table which does NOT involve the primary key (Invoice_ID) of the first one. Based on the non PK columns in the first table (namely Product_ID, Date_Of_Supply, Quantity, and Value_Of_Product), it is not clear that any of these are dependent on each other.

As a general rule of thumb, if you have a table where all non PK columns are dependent solely on the PK column of that table, it is already in 2NF.

Upvotes: 0

Related Questions