# Normalization in data modeling

Say you have a service where users can add products they like to a wish list, representing this with JSON, you might expect something like this:

```json
[
  {
    "user_id": 3,
    "email": "foo@example.com",
    "wishlist": [
      {
        "product_id": 323,
        "product_price": 3.29
      },
      {
        "product_id": 4942,
        "product_price": 15.99
      }
    ]
  },
  {
    "user_id": 7,
    "email": "bar@example.com",
    "wishlist": [
      {
        "product_id": 87,
        "product_price": 8.98
      },
      {
        "product_id": 4942,
        "product_price": 15.99
      }
    ]
  }
]
```

However, notice that there are two copies of the product with id 4942, this is common with the EAV way of modeling the data, it is also referred to as "denormalized" data. To get a better idea of what that means, let's normalize the above example using relations:

### the users relation
| user_id | email |
|---------|-------|
| 3       | foo@example.com |
| 7       | bar@example.com |

## the products relation

| product_id | product_price |
|------------|---------------|
| 323        | 3.29          |
| 4942       | 15.99         |
| 87         | 8.98          |

And to tie the two together, the

## the wishlist relation
| user_id | product_id |
|---------|------------|
| 3       | 323        |
| 3       | 4942       |
| 7       | 87         |
| 7       | 4942       |


So, this way you avoid unncessary duplication, you store the users in their own table, the products in their own table, and then use a third table to store the links between them. This will allow for much more efficient storage of data, and also help with consistency. For example, if product 4942 goes on sale, the denormalized form only has to update the price for the single record in the products table. By contrast, in the denormalized (JSON object above) form, you'd have to iterate over all the objects, dig into each of the wishlists and update the price in all of them.

## Querying
To query out the full records (JOINed together), you could run a query like:

```SQL
SELECT u.user_id, u.email, p.product_id, p.product_price
FROM users u
JOIN wishlist w
JOIN products p
ON u.user_id = w.user_id AND
   w.product_id = p.product_id
```


| user_id | email | product_id | product_price |
|---------|-------|------------|---------------|
| 3       |foo@example.com | 323        | 3.29 |
| 3       |foo@example.com | 4942       | 15.99|
| 7       |bar@example.com | 87         | 8.98 |
| 7       |bar@example.com | 4942       | 15.99|