Should I create a new table into my database for this?

On my database I have among other tables, products. Products table contains the id, name, description and some other data about the product. I want to add also a category.

Should I create a new table named 开发者_StackOverflow中文版category with and id and the name of each category, and have into the products a *category_id* that will refers to the id of category, or should I have the name of the category on each row of products ?

On the first case I will have to use JOIN. Will this have a serious impact on the performance?

By defining the categories in their own table you can:

• Rename categories
• Dynamically generate lists of categories for picking from
• Add descriptions of categories
• Add new categories

… and so on, without having to update every bit of category related code each time you modify them.

So yes, add a table.

Will this have a serious impact on the performance?

Probably not.

Yes you should keep your data normalized. I think create a new table named category is a good idea.

Will categories tend to change over time? In most instances that will be the case, so you're usually better off with a separate Categories table and a foreign key (FK) between the two tables. You can then add or change categories simply with data changes. Otherwise, you'll want to put a check constraint on the category name column in your table to make sure that you don't get junk data in there and that becomes much harder to maintain.

With proper indexing, the join should only have a minimal cost. Also, keep in mind that you won't always necessarily need to join the tables. You'll only need to join them when you want the actual category name as part of your result set. For example, if you have a look-up box on your front end with the categories and their IDs then your select of the Products only needs to return the category ID values and you don't need to even bother with a join.

The stock answer is "it depends". It depends on how the data is used, will be used, might be used. But once you get through all the discussions and arguments, for something this simple and prominent in your data, nine times out of eight you will be better off properly normalizing the data (i.e. Category table, CategoryId columns, and foreign key).

Only add a table if you require the extra functionality this level or normalization provides. If you need to rename categories, or list them then yes this is a good candidate. If not then why bother with it, it will make your database slightly harder to read.

Join will most probably not be a performance impact but if you do not need it now, try not to be a victim of over-design.