优化数据库设计中的表

Question

I have 3 tables Product, Color and Category. Color and category table has just name field. So I need to optimize number of tables. Because For retrieving data for product, I need to get data from all of these tables. Instead of it, I just want something like single table. So Everything can be retrieve from Single table.

In short, Looking for way to adjust color and category name in product table with Dynamic Insert and update functionality.

Product Table

product_id | int
name       | varchar
description| text
category   | cat_id
color      | color_id
size       | size_id

Color Table

color_id   | int
color      | varchar  

Category Table

cat_id     | int
category   | varchar  

For retrieving data form Product table, I need to write JOIN, Because color and category have data for product table. and more Join query results more loading time. So need to optimize number of tables.

Answer 1

Your current structure is good enough to hold product information with single color and category. Right now there is no need to optimize table structure. It look like perfect database structure for this.

You can create view to avoid writing joins every time.