Designing a Lookup Table for Efficient Data Categorization

Designing a Lookup Table for Categorical Data

Write a SQL query to design a lookup table for categorical data.

Solution:

-- Lookup table for categories.
CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY,
    CategoryName VARCHAR(100)
);

-- Main table referencing the lookup table.
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    CategoryID INT,
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);

Explanation:

• Purpose of the Query :
• The goal is to design a lookup table to store categorical data and reduce redundancy.
• Key Components :
• Categories: Stores category details independently.
• Products: Links products to categories via a foreign key.
• Why Use Lookup Tables? :
• Lookup tables centralize categorical data, making it easier to manage and update.
• They reduce storage requirements by avoiding repeated text values.
• Real-World Application :
• In inventory systems, lookup tables store product categories like "Electronics" or "Clothing."

Notes:

• Lookup tables improve consistency and simplify reporting.
• Use them for frequently reused attributes like statuses, types, or categories.
• Important Considerations:
• Ensure referential integrity between the main table and the lookup table.

For more Practice: Solve these Related Problems:

• Write a SQL query to design a lookup table for storing product statuses like "In Stock," "Out of Stock," and "Discontinued."
• Write a SQL query to create a lookup table for managing user roles such as "Admin," "Editor," and "Viewer."
• Write a SQL query to design a lookup table for storing payment methods like "Credit Card," "PayPal," and "Bank Transfer."
• Write a SQL query to create a lookup table for tracking order statuses such as "Pending," "Shipped," and "Delivered."

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Designing a Weak Entity for Dependent Data.
Next SQL Exercise: Designing a History Table for Auditing Changes.