2nd & 3rd Form Tips

In order to conform to the 2nd & 3rd normal form by removing all duplicated data there is a tip we can do to easily check for this: Check every field one by one and think can the field contain the same data multiple times and does it matter? Let's take a look at a couple of examples:

Example 1: Table Cars

Fields: carID (int, primary), Name (unique, varchar), Description (varchar), Price (int), Manufacturer (varchar)

carID is the primary key so it is always unique. As the name of the car is unique it is there only once. The description is probably never the same for the cars. Price can be the same for two cars, but does it matter? Not really. Price is just related to that one car.

The manufacturer is now interesting information in this example as two cars can have the same manufacturer. And in this case, it really does matter. This causes duplicated data. In order to fix this, we would need to create 2 new tables called Manufacturer and CarManufacturers (associative entity)

Example 2: Table Games

Fields: gameId (int, primary), name (varchar), genre (varchar), subGenre (varchar), price (int), amountOfReviewsWritten (int)

gameId, name and price are the same as in the example 1, so no duplication occurs there. What about the amountOfReviewsWritten? Well, that one is only a number that keeps track of the current amount of reviews written, so it can have duplicated data but doesn't really matter. In large databases, we would probably store this information also in a separate database as a cache item.

Genre and subGenre are interesting ones. Can one game belong to multiple genres? Yes. Does it matter? Yes. Also, the subGenre is pretty much a genre but one level deeper, so we would want to nest the subGenre below the genre. In order to fix this we would need to create associative entity tables for Genres, subGenres, and then gameGenres and gameSubGenres tables to link this data.