Normalizing Products > Category relation in SQL Server

Question

I am creating a SQL Server database and I have a question about one relation. I have the two entities Product and Category. The relation is one to many, so one product can have one category, but one category can have many products.

Which way is normalized and why (which normalization form)?:

1. Product { productId, productName, productPrice }
   Category { categoryId, Name }
   

2. Product { productId, productName, productPrice, categoryName }
   

If Category has more attributes and not only name I would make another table without doubt. But in this case I am not sure whether I have to make new table since it contains only name or leave it as varchar column. Isn't that going to create a lot more columns and take more space in database?

Answer 1

I think you should create the following two tables:

1. Category { categoryId, Name }
2. Product { productId, categoryId, productName, productPrice }

Considering that the categoryId in category table is a Primary Key, you should creata a foreign key constraint on categoryId in the Products table referencing to categoryId in the category table.