CODEWITHSUNDEEP
databasedatabase-design
LionM
LionM

Reputation: 1

Database design : avoid redundancy with a one to many relationship

I have two tables : Cateogy and Product

Category : id (autoincrement), label
Product : id (autoincrement), label, image (blob), category_id(foreign key)

So following that design, every time I add a category to a product, that means that the product in somehow duplicated but the only value that will change is the "category_id". I really find it redundant :

------------------
| Category       |
-----------------------
| id | label          |
-----------------------
| 1  | entertainment  |
| 2  | professional   |
| 3  | kitchen        |
-----------------------

------------------------------------------
| Product                                |
------------------------------------------
| id | label     | image | category_id   |
------------------------------------------
| 1  | computer  | [BLOB]| 1             |
| 2  | computer  | [BLOB]| 2             |
| 3  | TV        | [BLOB]| 1             |
------------------------------------------

Question : is there a way to get rid of redundancy from the product table and avoid to duplicate the product so that I can add a category ?

Regards

Upvotes: 0

Views: 419

Answers (2)

Erwin Smout
Erwin Smout

Reputation: 18408

The structure that you have literally says that each product can be of one category. So there is just no question of "adding a category to a product". If it is the case that "adding a category to a product" is a requirement (meaning it must be possible for a product to have/be associated with/... >1 category) then the database structure is simply wrong and must be revised.

Upvotes: 2

user1352669
user1352669

Reputation: 23

  1. Remove Category column from Product table.
  2. Create a new table called “Product Categories” with columns: product ID (foreign), category ID (foreign)

Upvotes: 2

Related Questions