Optimize Tables in Database Design

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.

They must be dynamic. Admin can Insert| Update|delete from backend.@etsa — Dhruvang Gajjar
– Dhruvang Gajjar, Commented Jul 12, 2017 at 10:10
Your schema looks reasonable to me. What is your actual question? — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 12, 2017 at 10:11
I need to reduce number of tables. 3 tables to 1 table. @TimBiegeleisen — Dhruvang Gajjar
– Dhruvang Gajjar, Commented Jul 12, 2017 at 10:12
@Dhruvang Doing so would mean denormalizing your schema, which would waste a lot of space. To be clear here: there is nothing wrong with doing joins provided the database and query are tuned properly. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 12, 2017 at 10:20

Virendra Jadeja · Accepted Answer · 2017-07-12 10:27:26Z

3

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.

answered Jul 12, 2017 at 10:27

Virendra Jadeja

8831 gold badge10 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

etsa Over a year ago

and you can (it depends...) have better performances creating indexes on product table (one with color_id and one with cat_id)

Collectives™ on Stack Overflow

Optimize Tables in Database Design

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related