Is it Okay to Store CategoryName in Products?
Is it Okay to Store CategoryName in Products?
Categories1 Table:
Products1 Table:
No, it is not recommended to store CategoryName directly in the Products table. Here's why:
Reasons:
1. Redundancy:
If multiple products belong to the same category (e.g., "Electronics"), then CategoryName will be repeated many times in the Products table, leading to data duplication.
2. Update Anomalies:
If the category name needs to be updated (e.g., changing "Electronic" to "Electronics"), we would have to update it in multiple rows, which increases the risk of inconsistent data.
3. Normalization Violation (2NF):
Including CategoryName in Products violates the Second Normal Form (2NF), because a non-key attribute (CategoryName) depends on another non-key attribute (CategoryID), not the primary key.
4. Referential Integrity:
It's better to store category details in a separate table (like Categories) and link them using a foreign key (CategoryID) in the Products table.
Query Explanation:
-
Step 1: Create
Categories1table-
This table holds unique category names like "Electronics" and "Audio".
-
It helps in avoiding repetition and updating centrally.
-
-
Step 2: Insert Category Data
-
Only 2 rows: one for Electronics and one for Audio.
-
Each has a
CategoryIDas the primary key.
-
-
Step 3: Create
Products1table-
This table stores products like Laptop, Tablet, etc.
-
Instead of directly using
CategoryName, it referencesCategoryIDfrom theCategories1table. -
A foreign key ensures that each product is linked to a valid category.
-
-
Step 4: Insert Product Data
-
Products are inserted with a reference to the
CategoryID. -
For example, ‘Laptop’ and ‘Tablet’ both belong to CategoryID 1 (Electronics).
-
-
Step 5: Join the Tables
-
A JOIN query is used to fetch
ProductID,ProductName,CategoryName, andUnitPrice. -
This joins
Products1withCategories1on matchingCategoryID.
-
SQL Query:
-- Create the Categories table (to avoid redundancy and maintain normalization)
CREATE TABLE Categories1 (
CategoryID INT PRIMARY KEY,
CategoryName VARCHAR(50)
);
-- Insert unique category data
INSERT INTO Categories1 VALUES
(1, 'Electronics'),
(2, 'Audio');
-- Create the Products table with a foreign key to CategoryID
CREATE TABLE Products1 (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
UnitPrice DECIMAL(10,2),
CategoryID INT,
-- IMPORTANT: Foreign key ensures referential integrity
FOREIGN KEY (CategoryID) REFERENCES Categories1(CategoryID)
);
-- Insert products with reference to CategoryID
INSERT INTO Products1 VALUES
(1, 'Laptop', 50000.00, 1),
(2, 'Tablet', 30000.00, 1),
(3, 'Headphones', 5000.00, 2),
(4, 'Speaker', 7000.00, 2);
-- Display final output using JOIN to fetch CategoryName from Categories table
SELECT
p.ProductID,
p.ProductName,
c.CategoryName,
p.UnitPrice
FROM
Products1 p
JOIN
Categories1 c ON p.CategoryID = c.CategoryID;
Output:
